Access work

PPaaggee 11 ooff 33

Microsoft Access 2016 – Final Exam Part 1 Creating an Electronic Accessories Database

Purpose: To demonstrate the ability to create a database, queries, and a form. 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:

1. Create a new database to store the two tables related to the electronics store. Call the database,

GetItNow Electronics.

2. Create the sales representatives table using the structure shown in Table A1A – 1. Make the Sales Rep ID the primary key. Use the name, Sales Rep, for the table.

3. Add the data shown in Table A1A – 2 to the Sales Rep table.

4. Use the name, Sales Rep for the table.

5. Create the Outlets table using the structure shown in Table A1A – 3. Make the Outlet ID the primary key. Use the name, Outlets for the table.

6. Add the data shown in Table A1A – 4.

7. Save the Outlets table.

8. Create a query using the Simple Query Wizard for the Sales Rep table. Include the Sales Rep ID,

Full Name, Outlet ID, Quota, and YTD Sales in the query. Save the query as Sales Rep Query.

9. Open the Sales Rep Query and add a criterion to limit retrieval to those sales representatives

located in outlet GINE 11. Save the query as Sales Rep – GINE 11 Query.

10. Open the Sales Rep Query and add a criterion to limit retrieval to those sales representatives who

have the number “3” anywhere in their Sales Rep ID. Save the query as Sales Rep – ID with 3

Query.

11. Open the Sales Rep Query and add a criterion to limit retrieval to those sales representatives

located in outlet GINE11 and YTD Sales is greater than $3,000,000. Save the query as Sales Rep

– GINE 11 and Sales Over 3 Million Query.

12. Open the Sales Rep Query and add a criterion to limit retrieval to those sales representatives

located in outlet GINE 11 or located in outlet GINE17. Save the query as Sales Rep – GINE 11

or GINE 17.

13. Create a simple form for the Sales Rep table. Use the name, Sales Rep Form, for the form.

14. Upload the database to the Access Final Exam Part 1 link in Blackboard.

PPaaggee 22 ooff 33

 Structure of the Sales Rep table --** Do this in Design View

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

Hire Date Date/Time

Outlet ID Short Text 7 Outlet ID

Quota Currency

YTD Sales Currency Year to date sales amount

Table A1A – 1

 Data for the Sales Rep table---** Do this in Datasheet View

Sales Rep ID Name Gender Age Hire Date Store ID Quota YTD Sales

321 ENTER YOUR

LAST NAME,

FIRST NAME

ENTER

YOUR

GENDER

24 3/12/2001 GINE 11 3,656,640 3,336,300

101 Battistini, Jorge M 26 7/2/2001 GINE 15 3,371,092 1,960,932

205 Lambert, Paul M 35 3/16/2002 GINE 11 1,768,442 1,525,237

613 Brunell, Candace F 36 6/1/2002 GINE 17 3,000,264 2,078,386

475 Dunn, Alan M 39 6/6/2002 GINE 26 1,652,836 1,432,701

125 Lyon, Jody F 27 8/3/2002 GINE 15 2,280,900 1,548,311

324 Bulger, Gloria F 25 12/23/2002 GINE 15 2,651,270 2,451,454

140 Lopez, Maria F 25 6/3/2004 GINE 17 1,837,213 1,563,140

285 Dillig, Henry M 42 11/21/2006 GINE 11 3,899,402 1,916,753

512 Kahn, Ramesh M 44 4/6/2007 GINE 26 3,426,535 3,292,270

678 Chiu, Leslie F 36 7/15/2007 GINE 26 3,218,514 2,514,181

287 Wochos, Lisa F 30 10/21/2007 GINE 15 2,648,883 2,188,106

465 Cataldo, Lydia F 25 11/15/2007 GINE 11 3,668,028 2,846,910

Table A1A – 2

PPaaggee 33 ooff 33

 Structure of the Outlets table----**Do this in Design View 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 – 3

Data for the Outlets table----Do this in Datasheet View

OOuuttlleett IIDD AAddddrreessss CCiittyy SSttaattee ZZiipp

GINE 11 1298 Western Court Bedlam CA 34896

GINE 15 25 Parkington Palace Southtown CA 39746

GINE 17 1212 Mocking Bird Ambervson CA 38957

GINE 26 774 Oakland Parkville CA 38974

Table A1A – 4