hady_EX19_AC_CH04_GRADER_ML2_HW1.zip
Exp19_Access_Ch04_ML2 – Benefit Auction 1.0
Access Chapter 4 Mid-Level 2 – Benefit Auction
Project Description:
You are helping to organize a benefit auction to raise money for families who lost their homes in a natural disaster. The information for the auction is currently stored in an Excel spreadsheet, but you have volunteered to import it to Access. You will create a database that will store the data from Excel in an Access database. You will create a form to manage the data-entry process. You also create two reports: one that lists the items collected in each category and one for labels so you can send the donors a thank-you letter after the auction.
Start Access. Open the downloaded Access file named Exp19_Access_Ch4_ML2_Auction.accdb. Grader has automatically added your last name to the beginning of the filename. Save the file to the location where you are storing your files.
Open the Items table in Design view. Change the ID Field Name to ItemID. Add a second field named Description. Accept Short Text as the data type for the Description field and change the field size to 50.
Enter the remaining field names in the table (in this order): DateOfDonation, Category, Price, DonorName, DonorAddress1, and then DonorAddress2. Change the data type of the DateOfDonation field to Date/Time and the Price field to Currency. Accept Short Text as the data type for the remaining fields.
Open Excel, and then open the file Items.xlsx. Examine the length of the Category, DonorAddress1, and DonorAddress2 columns. Return to Access. Change the field size for the Category to 15, DonorAddress1 to 25, and DonorAddress2 to 30. Save the table, and switch to Datasheet view.
Copy and paste the 26 rows from the Excel spreadsheet into the Items table. AutoFit all of the column widths so all data is visible. Save and close the table.
Verify that the Items table is selected in the Navigation Pane. Create a new form using the Form tool.
Select all of the fields and labels in the Detail section of the form. Change the layout of the form to a Tabular Layout. With all of the fields selected, switch to Design view and use the Property Sheet to set their widths to 1.3″. Change the width of the ItemID, Category, and Price columns to 0.75″.
Add conditional formatting so that each Price that is greater than 90 has a font color of Green (in the first row, under Standard Colors). (Hint: Search Conditional Formatting in the Tell me box). Save the form as Auction Items Form.
Switch to Form view and create a new record. Enter iPad as the Description; 12/31/2018 as the DateOfDonation; House as the Category; $400 as the Price; Staples as the DonorName; 500 Market St as the DonorAddress1; and Brick, NJ 08723 as the DonorAddress2.
Add a sort to the form, so the lowest priced items display first. Save and close the form.
Select the Items table in the Navigation Pane and create a report using the Report Wizard. Include all fields except the two donor address fields, group by Category, include the Sum of Price as a Summary Option, accept the default layout, and then save the report Auction Items by Category.
Switch to Layout view. Resize the DateOfDonation control so that the left edge of the control aligns with the left edge of the column label. Select the Price and Sum of Price controls and increase the width to 0.75″. Select any value in the DonorName column and drag the left edge of the controls to the right to decrease the width of the column. Preview the report to verify the column widths are correct.
Switch to Layout view, and then sort the report so the least expensive item is displayed first in each group. Save and close the report.
Select the Items table in the Navigation Pane. Create mailing labels based on the Avery 5660 template. (Hint: Search Labels in the Tell me box and then click the Labels tool in the results.) Place DonorName on the first line, DonorAddress1 on the second line, and DonorAddress2 on the third line. Sort the labels by DonorName. Name the report Donor Labels. After you create the labels, display them in Print Preview mode to verify that all values will fit onto the label template. Close the label report.
Close all database objects. Close the database and then exit Access. Submit the database as directed.
EX19_AC_CH04_GRADER_ML2_HW_Instructions x
Grader – Instructions Access 2019 Project
Exp19_Access_Ch04_ML2 – Benefit Auction 1.0
Project Description:
You are helping to organize a benefit auction to raise money for families who lost their homes in a natural disaster. The information for the auction is currently stored in an Excel spreadsheet, but you have volunteered to import it to Access. You will create a database that will store the data from Excel in an Access database. You will create a form to manage the data-entry process. You also create two reports: one that lists the items collected in each category and one for labels so you can send the donors a thank-you letter after the auction.
Steps to Perform:
Step
Instructions
Points Possible
1
Start Access. Open the downloaded Access file named Exp19_Access_Ch4_ML2_Auction.accdb. Grader has automatically added your last name to the beginning of the filename. Save the file to the location where you are storing your files.
0
2
Open the Items table in Design view. Change the ID Field Name to ItemID. Add a second field named Description. Accept Short Text as the data type for the Description field and change the field size to 50.
5
3
Enter the remaining field names in the table (in this order): DateOfDonation, Category, Price, DonorName, DonorAddress1, and then DonorAddress2. Change the data type of the DateOfDonation field to Date/Time and the Price field to Currency. Accept Short Text as the data type for the remaining fields.
10
4
Open Excel, and then open the file Items.xlsx. Examine the length of the Category, DonorAddress1, and DonorAddress2 columns. Return to Access. Change the field size for the Category to 15, DonorAddress1 to 25, and DonorAddress2 to 30. Save the table, and switch to Datasheet view.
9
5
Copy and paste the 26 rows from the Excel spreadsheet into the Items table. AutoFit all of the column widths so all data is visible. Save and close the table.
10
6
Verify that the Items table is selected in the Navigation Pane. Create a new form using the Form tool.
6
7
Select all of the fields and labels in the Detail section of the form. Change the layout of the form to a Tabular Layout. With all of the fields selected, switch to Design view and use the Property Sheet to set their widths to 1.3″. Change the width of the ItemID, Category, and Price columns to 0.75″.
10
8
Add conditional formatting so that each Price that is greater than 90 has a font color of Green (in the first row, under Standard Colors). (Hint: Search Conditional Formatting in the Tell me box). Save the form as Auction Items Form.
10
9
Switch to Form view and create a new record. Enter iPad as the Description; 12/31/2018 as the DateOfDonation; House as the Category; $400 as the Price; Staples as the DonorName; 500 Market St as the DonorAddress1; and Brick, NJ 08723 as the DonorAddress2.
10
10
Add a sort to the form, so the lowest priced items display first. Save and close the form.
5
11
Select the Items table in the Navigation Pane and create a report using the Report Wizard. Include all fields except the two donor address fields, group by Category, include the Sum of Price as a Summary Option, accept the default layout, and then save the report Auction Items by Category.
7
12
Switch to Layout view. Resize the DateOfDonation control so that the left edge of the control aligns with the left edge of the column label. Select the Price and Sum of Price controls and increase the width to 0.75″. Select any value in the DonorName column and drag the left edge of the controls to the right to decrease the width of the column. Preview the report to verify the column widths are correct.
3
13
Switch to Layout view, and then sort the report so the least expensive item is displayed first in each group. Save and close the report.
5
14
Select the Items table in the Navigation Pane. Create mailing labels based on the Avery 5660 template. (Hint: Search Labels in the Tell me box and then click the Labels tool in the results.) Place DonorName on the first line, DonorAddress1 on the second line, and DonorAddress2 on the third line. Sort the labels by DonorName. Name the report Donor Labels. After you create the labels, display them in Print Preview mode to verify that all values will fit onto the label template. Close the label report.
10
15
Close all database objects. Close the database and then exit Access. Submit the database as directed.
0
Total Points
100
Created On: 07/11/2019 1 Exp19_Access_Ch04_ML2 – Benefit Auction 1.0
hady_Exp19_Access_Ch4_ML2_Auction.accdb
ID
ID mSysRowId
1
Items.xlsx
items
ItemID Description Donation Date Category Price Donor Name Donor Address 1 Donor Address 2
1 Cake 11/1/16 Food $20.00 Rabiah Hannah 553 Madsen’s Place Paulsboro, NJ 08066
2 Picture 11/1/16 House $10.50 Alfredo Ramirez 66 England Circle Ventnor City, NJ 08406
3 Men’s Haircut 11/8/16 Service $15.00 Jasmine Rangan 887 Aurora Street Pomona, NJ 08240
4 Set of knives 11/10/16 House $35.95 Takeshi Ito 671 Homer Drive Little Ferry, NJ 07643
5 Dinner for two 11/10/16 Certificate $64.50 Kyong Shin 829 Klevin Street Rockaway, NJ 07866
6 Lawn service 11/11/16 Service $100.00 Mouna Bishara 703 Mc Gowan Street Helmetta, NJ 08828
7 Photo session 11/11/16 Service $75.00 Gang Liu 926 Loon Circle Sussex, NJ 07461
8 Gift card 11/17/16 Certificate $100.00 Kiyoshi Sasaki 1159 Town Park Circle Point Pleasant Beach, NJ 08742
9 Gift card 11/17/16 Certificate $50.00 Kiyoshi Sasaki 1159 Town Park Circle Point Pleasant Beach, NJ 08742
10 Gift card 11/17/16 Certificate $25.00 Kiyoshi Sasaki 1159 Town Park Circle Point Pleasant Beach, NJ 08742
11 Brownies 11/20/16 Food $10.00 Akira Yamada 816 Clipper Ship Drive Little Silver, NJ 07739
12 Garden tools 11/23/16 House $125.00 Toshio Sasaki 1083 Evergreen Street Middletown, NJ 07748
13 Plants 11/26/16 House $95.00 Hui Lin 821 East 23Rd Street Princeton, NJ 08541
14 Flowers 11/26/16 House $54.50 Li Zhang 453 Sun Valley Drive Dumont, NJ 07628
15 Cleaning service 12/5/16 Service $95.00 Dalia Abboud 148 Cobblecreek Circle Cream Ridge, NJ 08514
16 Clothes 12/16/16 Clothes $100.00 Yasuo Tanaka 659 Lindblad Avenue Gloucester City, NJ 08030
17 Shoes 12/16/16 Clothes $25.00 Kenji Yamamoto 522 Bridle Circle Hibernia, NJ 07842
18 Umbrella 12/16/16 House $13.50 Kwan Jeong 167 Ramona Street Trenton, NJ 08641
19 Blender 12/19/16 House $45.00 Soranlly Lopez 477 Nabesna Circle Allendale, NJ 07401
20 Carpet cleaning 12/21/16 Service $135.00 Yukio Ito 89 Ostovia Circle Glen Rock, NJ 07452
21 Car detailing 12/24/16 Service $150.00 Kyu Shin 876 Canterbury Way Ventnor City, NJ 08406
22 Six steaks 12/24/16 Food $75.00 Dario Perez 401 Sanak Circle Mauricetown, NJ 08329
23 Dishware 12/25/16 House $44.00 Rajesh Bhatnagar 855 Tiffin Circle Glen Gardner, NJ 08826
24 Target gift card 12/26/16 Certificate $50.00 Target 1099 Venus Way Hampton, NJ 08827
25 Designer coat 12/27/16 Clothes $99.00 Rajesh Bhatnagar 855 Tiffin Circle Glen Gardner, NJ 08826
26 Used computer 12/28/16 House $150.00 Loelia Hamade 35 Tree Top Lane Totowa, NJ 07511
Sheet2
Sheet3