Microsoft Office Tutorials and References
In Depth Information
| Review Assignments
Practice the skills you
learned in the tutorial
using the same case
Data File needed for the Review Assignments: Products.accdb
In the Review Assignments, you’ll create several new queries and enhance the table
design in a database that contains information about the suppliers that Belmont Land-
scapes works with on its landscape design projects. Complete the following steps:
1. Open the Products database, which is located in the Level.02\Review folder pro-
vided with your Data Files.
2. Modify the first record in the tblCompany table datasheet by changing the
ContactFirstName and ContactLastName field values to your first and last names.
Close the table.
3. Create a query to find all records in the tblCompany table in which the City field
value starts with the letter H. Display all fields in the query recordset, and sort in
ascending order by CompanyName. Save the query as qryHSelectedCities , run the
query, and then close it.
4. Make a copy of the qryHSelectedCities query using the new name
qryOtherSelectedCities . Modify the new query to find all records in the tblCompany
table in which the City field values are not Lansing, Rockford, or Zeeland. Save and
run the query, and then close it.
5. Create a query to find all records from the tblProduct table in which the Color field
value is Black, White, or Grey. Use a list-of-values match for the selection criteria.
Display all fields in the query recordset, and sort in descending order by Price. Save
the query as qrySelectedColors , run the query, and then close it.
6. Create a query to display all records from the tblCompany table, selecting the
CompanyName, City, and Phone fields, and sorting in ascending order by
CompanyName. Add a calculated field named ContactName as the first column that
concatenates the ContactFirstName, a space, and the ContactLastName. Set the
Caption property for the ContactName field to Contact Name . Save the query as
qryCompanyContacts , run the query, resize the Contact Name column to its best fit,
and then save and close the query.
7. Create a parameter query to select the tblProduct table records for a Color field
value that the user specifies. If the user doesn’t enter a Color field value, select all
records from the table. Display the ProductType, Price, Color, and DiscountOffered
fields in the query recordset, sorting in ascending order by Price. Save the query as
qryColorParameter . Run the query and enter no value as the Color field value, and
then run the query again and enter Wood as the Color field value. Close the query.
8. Create a find duplicates query based on the tblProduct table. Select ProductType as
the field that might contain duplicates, and select the ProductID, CompanyID, Price,
and DiscountOffered fields as additional fields in the query recordset. Save the
query as qryDuplicateProductTypes , run the query, and then close it.
9. Create a find unmatched query that finds all records in the tblCompany table for
which there is no matching record in the tblProduct table. Display the CompanyID,
CompanyName, City, Phone, ContactFirstName, and ContactLastName fields
from the tblCompany table in the query recordset. Save the query as
qryCompaniesWithoutMatchingProducts , run the query, and then close it.
10. Make a copy of the qryPricesWithDiscountAmounts query using the new name
qryTopPricesWithDiscountAmounts . Modify the new query to use the Top Values
property to select the top 25% of records. Save and run the query, and then close it.