University of North Texas Module 5 Healthy Myles Fitness Center Excel Project
Create a query to find all records in the tblClient table in which the Phone field value starts with the area code 716. Include the following options:Display all fields from the tblClient table in the query in the following order: ClientID, ClientFirst, ClientLast, Address, City, State, Zip, Phone, BirthDate, Gender. Sort the query in ascending order by the ClientLast field. Create a query to find all records in the tblClient table in which the City field value is not Buffalo, Perry, or Castile. Include the following options:Display all fields from the tblClient table in the query in the following order: ClientID, ClientFirst, ClientLast, Address, City, State, Zip, Phone, BirthDate, Gender. Add a condition to find all records in the tblClient table in which the City field values are not “Buffalo”, “Perry”, or “Castile”. Use a list-of-values match for the selection criteria. Sort the query in ascending order by the City field. Create a query to find all records from the tblEmployee table in which the MonthlySalary value is 1500, 1850, or 3550. Use a list-of-values match for the selection criteria. The query should have the following options:Display all fields from the tblEmployee table in the query in the following order: EmployeeID, LocationID, First, Last, Title, MonthlySalary. Sort the query in descending order by the MonthlySalary field.Create a query to display all records from the tblTrainer table with the following options:Display only the LocationID, Specialty, and Certification fields (in that order) from the tblTrainer table in the query.Sort the query in ascending order by the LocationID field. Add a calculated field named TrainerName as the first column in the query that concatenates the First field value, a space, and the Last field value. Set the Caption property for the TrainerName field to Trainer Name. (Hint: Do not type the period.)Save the query as qryTrainerSpecialties, run the query, and then resize the TrainerName column to best fit the data it contains.Create a parameter query to select the tblTrainer table records for a Certification field value that the user specifies. The query should have the following options:Display the LocationID, First, Last, Specialty, and Certification fields (in that order) in the query. Use Enter the certification: as the prompt associated with the Certification field prompt. If the user doesn’t enter a Certification field value, the parameter query should select all records from the tblTrainer table. Sort the query in ascending order by the Last field.Save the query using qryCertificationParameter as the name.Create a find duplicates query based on the tblClient table with the following options:Select the Address field as the field that might contain duplicates.Select the ClientID, ClientFirst, ClientLast, and Phone fields (in that order) as additional fields in the query recordset. Create a find unmatched query that finds all records in the tblTrainer table for which there is no matching record in the tblBilling table. Display the TrainerID, LocationID, First, Last, and HireDate fields from the tblTrainer table in the query recordset. Save the query as qryTrainersWithoutMatchingBilling and then close it.Create a query to display all fields from the tblEmployee table in the following order: EmployeeID, LocationID, First, Last, Title, and MonthlySalary, sorted in descending order by the MonthlySalary field. Use the Top Values property to select the top five records. Save the query as qryTop5Salaries, run the query, and then close it.Open the tblLocation table in Design View. Change the ManagerID field to a lookup field with the following options:Specify that the lookup field values will come from another table or query.Select the Last field, and then select the EmployeeID field from the tblEmployee table.Sort the values in ascending order by the Last field.Confirm the Hide the key column (recommended) option is checked.Resize the Lookup column to best fit the data it contains.Accept the default label for the Lookup column.Save your changes, and then view the tblLocation table in Datasheet View.Change the ManagerID field value for the record with Location ID A to Carlson.Open the tblBilling table in Design View. Create a field validation rule for the Sessions field to only allow values greater than 0. Enter Clients must participate in at least 1 session. (including the period) as the validation text. Save and close the tables, clicking Yes when warned about the data integrity rule.Open the tblClient table in Design View. Use the Input Mask Wizard to add an input mask to the Phone field. The input mask should use parentheses as separators for the area code, a space between the area code and the number, and a dash between the second and third groups of numbers, as in (123) 456-7890, with only the last seven digits required. Do not store the literal display characters if you are prompted to do so. Save the tblClient table and switch to Datasheet View. Change the Phone field value for the record with ClientID A10026 to (716) 555-0001.View the tblClient table in Design View, and then define a field validation rule for the Gender field in the tblClient table. Acceptable field values for the Gender field are F and M (in that order). Use the message Must be F or M to notify a user who enters an invalid Gender field value. Save the table, clicking Yes when warned about data integrity rules.Create a crosstab query based on the tblEmployee table with the following options:Use the LocationID field values for the row headings.Use the Title field values for the column headings.Use the sum of the MonthlySalary field values as the summarized value.Include row sums. Save the query using qryMonthlySalaryByTitle as the name. Save the query as qry716AreaCode, run the query, and then close it. Save the query with the name qryNotBuffaloPerryCastile, then run and close it. Save the query as qrySelectSalaries, run the query, and then close it. Save and close the query. Confirm the parameter query is working correctly by running the query and entering no value as the Certification field value. Then run the query again and enter NATA as the Certification field value. Close the query. Save the query as qryDuplicateHouseholds and then close it. Close the table. Switch to Datasheet View and test the field validation rule for the Gender field, making sure any tested field values are the same as they were before your test. (Hint: You can confirm this by retyping the correct value or by pressing the Esc key.) Close the table. Run the query, then save and close it.