Homework answers / question archive / New Perspectives Access 2016 | Module 5: SAM Project 1b GRMH Recreation Centers CREATING SPECIALIZED QUERIES AND ENHANCING A DATABASE   GETTING STARTED Open the file NP_AC16_5b_FirstLastName_1

New Perspectives Access 2016 | Module 5: SAM Project 1b GRMH Recreation Centers CREATING SPECIALIZED QUERIES AND ENHANCING A DATABASE   GETTING STARTED Open the file NP_AC16_5b_FirstLastName_1

MS Access

Share With

---

New Perspectives Access 2016 | Module 5: SAM Project 1b

GRMH Recreation Centers

CREATING SPECIALIZED QUERIES AND ENHANCING A DATABASE

 

GETTING STARTED

• Open the file NP_AC16_5b_FirstLastName_1.accdb, available for download from the SAM website.
• Save the file as NP_AC16_5b_FirstLastName_2.accdb by changing the “1” to a “2”.
  • If you do not see the .accdb file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.
  • Open the _GradingInfoTable table and ensure that your first and last name is displayed as the first record in the table. If the table does not contain your name, delete the file and download a new copy from the SAM website.

 

PROJECT STEPS

1. Create a query to find all records in the tblMember table in which the Address field contains the word “Lake”. Include the following options:
   1. Display the following fields from the tblMember table in the query in the following order: MemberID, FirstName, LastName, Address, City, State, Zip, Phone.
   2. Sort the query in ascending order by the LastName field in Design View.

Save the query as qryLake, run the query, and then close it.

1. Make a copy of the qryLake query, rename it qryNotGMW, and then make the following updates:
   1. Delete the criterion from the Address field.
   2. Add new criteria to find all records in the tblMember table in which the City field does not contain “Grand Rapids”, “Muskegon”, or “Whitehall”. Use a list-of-values match for the selection criteria.
   3. If the query is not already sorted, sort the query in ascending order by the LastName field in Design View.
   4. Save and run the query, and then resize the Address column to best fit.

Save and close the qryNotGMW query.

1. Create a query to find all records from the tblBilling table in which the Amount field value is 750, 800, or 850. Use a list-of-values match for the selection criteria. The query should have the following options:
   1. Display these fields from the tblBilling table in the query in the following order: BillingID, MemberID, TrainerID, StartDate, EndDate, Amount.
   2. In Design View, sort the query in descending order by the Amount field.

Save the query as qryAmount750-850, run the query, and then close it.

1. Create a query to display all records from the tblEmployee table with the following options:
   1. Display the EmployeeID, LocationID, and Title fields (in that order) in the query.
   2. Sort the query in ascending order by the Title field in Design View.
   3. Add a calculated field named EmpName as the first column in the query that concatenates the First field value, a space, and the Last field value.
   4. Set the Caption property for the EmpName field to Employee Name. (Hint: Do not type the period.)
   5. Save the query as qryEmployeeList, run the query, and then resize the Employee Name column to best fit.

Save and close the query.

1. Create a parameter query to select the tblMember table records for a Zip field value that the user specifies. Include the following options:
   1. Display the FirstName, LastName, Address, City, State, and Zip fields (in that order) in the query.
   2. Use Enter the 5-digit zip code: as the Zip field prompt. If the user doesn’t enter a Zip field value, the parameter query should select all records from the tblMember table.
   3. Sort the query in ascending order by the LastName field in Design View.
   4. Save the query as qryZipParameter. (Hint: Do not type the period.)

Confirm the parameter query is working correctly by running the query and entering no value as the Zip field value. Then run the query again and enter 49441 as the Zip field value. Close the query.

1. Create a find duplicates query based on the tblTrainer table with the following options:
   1. Select the Certification field as the field that might contain duplicates.
   2. Select the TrainerID, FirstName, LastName, LocationID, and Specialty fields (in that order) as additional fields in the query recordset.

Save the query as qryDuplicateCerts, and then close it.

1. Create a find unmatched query that finds all records in the tblMember table for which there is no matching record in the tblBilling table. The tables are linked by the common MemberID field. Display the MemberID, FirstName, LastName, and Phone fields (in that order) from the tblMember table in the query recordset. Save the query as qryMembersWithoutMatchingSales, and then close it.
2. Create a query to display the following fields from the tblBilling table in the order shown: BillingID, TrainerID, StartDate, EndDate, Amount, and Sessions, sorted in descending order by the Amount field in Design View. Use the Top Values property to select the Top 10%. Save the query as qryTop10PercentAmt, run the query, and then close it.
3. Open the tblEmployee table in Design View. Change the LocationID field to a lookup field with the following options:
   1. Specify that the lookup field values will come from another table or query.
   2. Select the LocationID field and the LocationName field from the tblLocation table.
   3. Sort the values in ascending order by the LocationName field.
   4. Confirm that the Hide key column (recommended) option is checked.
   5. Resize the lookup column to best fit.
   6. Accept the default label for the lookup column.
   7. Save the changes and view the tblEmployee table in Datasheet View.

Close the table.

1. Open the tblBilling table in Design View. Create a field validation rule for the Sessions field to only allow values greater than or equal to 1. Enter 1 session minimum as the validation text. Save and close the table. Click Yes in the Data Integrity rule prompt.
2. Open the tblMember 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 closing parenthesis and the number, an underscore (_) as the placeholder, and a hyphen 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 tblMember table and switch to Datasheet View. Change the Phone field value for the record with MemberID B22189 (Roger Chapman) to (616) 555-2233. Close the tblMember table.
3. View the tblTrainer table in Design View and define a field validation rule for the HireDate field. Acceptable field values for the HireDate field are >=1/1/2013. Use the message Must be on or after 1/1/2013 to notify a user who enters an invalid HireDate field value. Save the table. Click Yes in the Data Integrity rule prompt.

Switch to Datasheet View and test the field validation rule for the HireDate field. Make sure any tested field values are the same as they were before your testing. (Hint: You can confirm this by retyping the correct value or by pressing the Esc key.) Close the table.

1. Create a crosstab query using the tblBilling table with the following options:
   1. Use the TrainerID field values for the row headings.
   2. Use the MemberID field values for the column headings.
   3. Use the sum of the Amount field values as the summarized value.
   4. Include row sums.
   5. Save the query as qryAmountByTrainer. (Hint: Do not type the period.)

Run the query and then close it.

Save and close any open objects in your database. Compact and repair your database, close it, and exit Access. Follow the directions on the SAM website to submit your completed project.