Advanced PC Applications
1. Start Access and create a new blank database named “Retailers.”
2. Name the new table “Retailers” and then display it in Design view.
3. Edit the ID field to be “Retailer ID” and verify that it’s data type AutoNumber and that it has been selected as the primary key.
4. In Design view, add the remaining fields and corresponding data types:
|
Field Name |
Data Type |
|
Store Name |
Short Text |
|
Address |
Short Text |
|
City |
Short Text |
|
State |
Short Text |
|
Zip |
Short Text |
5. Save and then close the Retailers table.
6. Create a new table named “Sales Reps” and display it in Design view.
7. Edit the ID field to be “Sales Rep ID” and verify that it’s data type AutoNumber and that it has been selected as the primary key.
8. Add a field named “Retailer ID” and then select the Lookup Wizard as the Data Type.
a. Use the Wizard to select Retailers as the table for the Lookup field.
b. Click Retailer ID as the field with the values to choose from.
c. Sort the values in Ascending order.
d. Click Finish.
9. In Design view, add the remaining Sales Reps fields and corresponding data types:
|
Field Name |
Data Type |
|
First Name |
Short Text |
|
Last Name |
Short Text |
|
Job Title |
Short Text |
10. Save and close the Sales Reps table.
11. Create a Retailers form that includes the following information:
· Retailer ID
· Store Name
· Address
· City
· State
· Zip
12. The form should look similar to the figure below.
13. Create a Sales Reps form that includes the following information:
· Sales Rep ID
· Retailer ID
· First Name
· Last Name
· Job Title
14. The form should look similar to the figure below.
15. Use the Retailers form to populate the Retailers table with the records listed below.
|
Retailers Records |
|
|
Retailer ID |
1 |
|
Store Name |
Fishing World |
|
Address |
160 Trout Circle |
|
City |
Any Town |
|
State |
PA |
|
Zip |
16754 |
|
|
|
|
Retailer ID |
2 |
|
Store Name |
Everything Bait Shop |
|
Address |
1336 Ocean Parkway |
|
City |
Any Town |
|
State |
PA |
|
Zip |
16755 |
|
|
|
|
Retailer ID |
3 |
|
Store Name |
Major Sporting Goods |
|
Address |
14998 Atlantic Ave. |
|
City |
Any Town |
|
State |
PA |
|
Zip |
16759 |
16. Use the Sales Reps form to populate the Sales Reps table with the records listed below. Note that the Retailer ID is a lookup field and you’ll be able to select the value from a list.
|
Sales Reps Records |
|
|
|
|
|
Sales Rep ID |
1 |
|
Retailer ID |
2 |
|
First Name |
Marty |
|
Last Name |
Hu |
|
Job Title |
Owner |
|
|
|
|
Sales Rep ID |
2 |
|
Retailer ID |
1 |
|
First Name |
Chris |
|
Last Name |
Tryniski |
|
Job Title |
Fishing Dept. Rep |
|
|
|
|
Sales Rep ID |
3 |
|
Retailer ID |
1 |
|
First Name |
Karm |
|
Last Name |
Howard |
|
Job Title |
Outdoor Goods Manager |
|
|
|
|
Sales Rep ID |
4 |
|
Retailer ID |
3 |
|
First Name |
Liz |
|
Last Name |
Bennett |
|
Job Title |
Tackle Sales Rep |
|
|
|
|
Sales Rep ID |
5 |
|
Retailer ID |
3 |
|
First Name |
Turner |
|
Last Name |
Ward |
|
Job Title |
Store Manager |
17. Format Table Datasheet view for the Retailers and Sales Reps table, adjusting column widths as appropriate.
18. Create a query in Design view that displays complete contact information in alphabetical order by store name. The tables should already display a relationship because of the Lookup field. If not, join the tables appropriately to run the query correctly.
19. Run the query and save it as “Contacts.”
20. Close the query and any open tables and forms.
21. Close the Retailers database.