Electronic Accessories Database
Purpose: To demonstrate the ability to create a database with multiple tables, to build relationship between tables, and to produce queries, forms, and reports
Problem: An electronics store wants to keep track of its sales representatives for its various outlets. The database it will use consists of two tables. The Outlets table contains data on the particular outlet store. The Sales Rep table contains pertinent data about the electronics store’s sales representatives.
Instructions: The structure for the Outlets table is shown in Table A1A – 1 The structure for the Sales Rep table is shown in Table A1A – 2.
1. Open GetItNow Electronics.
2. Modify the structure of sales representatives table to that shown in Table A1A – 1. (No data will be lost.)
3. Modify the structure of the Outlets table to that shown in Table A1A – 2. (No data will be lost.)
4. In the Sales Rep table, change the Full Name on rep 321 to your name.
5. Create the following validation rules for the sales representatives table and save the changes.
a. Specify the legal values M and F for the Gender field. Include validation text.
6. Build a relationship between the tables enforcing referential integrity.
7. Create a query showing all fields for all the records in the Sales Rep table and add a criterion to limit retrieval to those sales representatives located in outlet GINE 11. Save the query as Query 1.
8. Create a query showing the Sales Rep ID, Outlet ID, and Quota for all records in the Sales Rep table. Save the query as Query 2.
9. Create a query showing the Sales Rep ID, Outlet ID, and Quota for all the male Sales Reps. Do not show the Gender in the results. Save the query as Query 3.
10. Create a query showing the Sales Rep ID, Full Name, Age, and Hire Date for all Sales Reps with a hire date after June 1, 2005. Save the query as Query 4.
11. Create a query showing the Sales Rep ID, Full Name, Outlet ID, Quota, and YTD Sales for all Sales Reps with a quota of more than $2,000,000 and Outlet ID of GINE 17. Save the query as Query 5.
12. Create a query showing the Sales Rep ID, Full Name, Outlet ID, and YTD Sales for all Sales Reps with a Sales Rep ID that begins with 6. Save the query as Query 6.
13. Create a query showing the Sales Rep ID, Full Name, Hire Date, and Quota for all Sales Reps with a hire date before July 2, 2004 or a quota of less than $2,500,000. Save the query as Query 7.
14. Create a query showing the Sales Rep ID, Full Name, Outlet ID, and YTD Sales for the top 4 Sales Reps with YTD Sales greater than $1,500,000 and less than $3,000,000. (Hint: Enter a criteria of “>1500000 And<3000000” for YTD Sales.) Save the query as Query 8.
15. Create a query showing the Sales Rep ID, Outlet ID, Full Name, and Hire Date of all records in the Sales Rep table sorted in ascending sequence by Full name. Using a parameter, ask the user to “Enter Outlet ID”, and use it to limit the records to just those which match the parameter. Save the query as Query 9.
16. Using all the fields, display all the records in the Sales Reps table sorted in descending sequence by Full Name within Outlet ID. Make Outlet ID the first field in the query. Save the query as Query 10.
17. Using both tables, display the fields Sales Rep ID, Outlet ID, Full Name, Address, City, and State. Save the query as Query 11.
18. Restrict the records retrieved in Query 11 to only Sales Reps with a Sales Rep ID beginning with a 1. Create a query showing the results. Save the query as Query 12.
19. From the Sales Rep table, display the Outlet ID and Quota for the highest quota with an Outlet ID of GINE 26. Save the query as Query 13.
20. From the Sales Rep table, display the average YTD Sales by Outlet ID. Save the query as Query 14.
21. If each Sales Rep is required to increase their quota by 15%, display the Sales Rep ID, Outlet ID, Quota, and New Quota for every record in the Sales Reps table. Format the new column into Currency. Save the query as Query 15.
22. Create a simple form for the Sales Rep table. Use the name, Sales Rep Form, for the title of the form. Make the fields of the form narrower. Save it with the same name.
23. Open your Sales Rep Form if you closed it. Outlet GINE 17 has hired a new sales representative and needs to add the record to the Sales Rep table. The new employee is Mike Smith. He is 35, and is hired today with a Sales Rep ID of 651. His quota is set initially to $1,500,000.
24. Create the report for the Sales Rep table. Add totals for the Quota and YTD Sales. Adjust the column widths so it will fit on one sheet of paper.
25. Set the database properties, Author to your name. Submit the database.
Structure of the Sales Rep table
|
Field Name
|
Data Type
|
Field Size
|
Primary Key?
|
Description
|
|
Sales Rep ID
|
Short Text
|
3
|
Yes
|
Sales Representative ID (Primary Key)
|
|
Full Name
|
Short Text
|
30
|
|
|
|
Gender
|
Short Text
|
1
|
|
Gender (M or F)
|
|
Age
|
Number
|
|
|
Integer
|
|
Hire Date
|
Date/Time
|
|
|
|
|
Outlet ID
|
Short Text
|
7
|
|
Outlet ID
|
|
Quota
|
Currency
|
|
|
|
|
YTD Sales
|
Currency
|
|
|
Year to date sales amount
|
Table A1A – 1
Structure of the Outlets table
|
Field Name
|
Data Type
|
Field Size
|
Primary Key?
|
Description
|
|
Outlet ID
|
Short Text
|
7
|
Yes
|
Outlet ID (Primary Key)
|
|
Address
|
Short Text
|
30
|
|
Outlet Address
|
|
City
|
Short Text
|
20
|
|
|
|
State
|
Short Text
|
2
|
|
|
|
Zip
|
Short Text
|
5
|
|
|
Table A1A – 2
