Microsoft Access
dhurba9212
Access2016Chapter6-LabTestA.docxEnhanced Microsoft Access 2016 Chapter 6 – Lab Test A
Advanced Report Techniques
Purpose: To demonstrate the ability to utilize special Access features in the creation of reports.
Problem: You have been asked to add tables to the HaveMore Electronics database as well as create some advanced reports using the new data.
Instructions: Open the Access_Chapter_6_-_Lab_Test_A_-_HaveMore_Electronics database file provided in a location specified by your instructor and execute the following tasks:
1. Create a table to contain inventory information for the outlets. Use the structure illustrated in Figure A6A – 1. Add the data shown in Figure A6A – 2 to the Inventory table.
2. Create a table to contain inventory items information for the outlets. Use the structure illustrated in Figure A6A – 3. Add the data shown in Figure A6A – 4 to the Items table.
3. Add the Inventory table to the Relationships window and establish a one-to-many relationship between the Outlets and the Inventory tables.
4. Add the Items table to the Relationships window and establish a one-to-many relationship between the Inventory and the Items tables. Print the Relationships window.
5. Create a query called Outlets and Sales Rep that joins the Outlets and Sales Rep tables. The query should display all fields in the Outlets table and the Sales Rep table except for the Outlet ID from the Sales Rep table.
6. Create a query called Inventory and Items that joins the Inventory and Items tables. The query should display all fields in the Inventory table and the Items table except for the Item ID from the Items table.
7. Create the report shown in Figure A6A – 5. The report uses the Outlets and Sales Rep query as the basis for the main report and the Inventory and Items query as the basis for the subreport. Use the name Outlet Master Report for the report. The report title has a Text Align property value of Distribute. The Border Width property is hairline and the subreport label is Inventory.
8. Have the Outline ID Header repeat on each page. In the footer, include the page number on the left and the current date on the right.
9. Print the report.
10. Close the database. Submit the results according to the specifications provided by your instructor.
|
Field Name Data Type Field Size Primary Key Description Outlet ID Short Text 6 Yes Outlet ID
Item ID Short Text 5 Yes Item ID Qty on Hand Number Number of items on hand Date Updated Date/Time Date inventory of item was last updated |
Figure A6A – 1, Structure of Inventory table
Figure A6A – 2, Data for Inventory table
|
Field Name Data Type Field Size Primary Key Description Item ID Short Text 5 Yes Item ID
Item Descr Short Text 100 Item description
Price Currency Cost of an item |
Figure A6A – 3, Structure of Items table
|
Item ID |
Item Descr |
Price |
|
T1587 |
BlueRay DVD |
269 |
|
L2458 |
DVD RW |
135 |
|
M9899 |
External Hard Drive |
179 |
|
P2933 |
HD Video |
248 |
|
Y9390 |
LaserJet LJ9900 |
189 |
|
U9893 |
Maximun CPU |
267 |
|
H4541 |
Palm Reader280 |
548 |
|
H2250 |
Ultimate Keyboard |
204 |
|
T8345 |
WiFi Router |
169 |
|
P4320 |
HD LCD |
147 |
|
U1165 |
Big Audio - Sound Kit |
325 |
|
Y2899 |
LGM Pad |
699 |
|
I9809 |
TKB Laptop |
875 |
|
N9300 |
LR Car Stereo |
451 |
|
B9839 |
OnCue TV |
689 |
Figure A6A – 4, Data for Items table
Figure A6A – 5
Page 2
© 2016 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
