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.