Microsoft Access Exercise
iwatbo
Access_2016_ACP_3.pdf
Access 2016: In Practice Page 1 of 6 Access Application Capstone Project 3
ACCESS 2016: IN PRACTICE—APPLICATION CAPSTONE PROJECT
Access 2016: Application Capstone Project 3 (Chapters 5-7)
For this project, you enhance the functionality of a database for Magnificent Meals. The database
contains a table to store meal information and another table to store order information. You add a
junction table to represent the many‐to‐many relationship between these tables. Next, add a subreport
to a main report and customize the subreport in Design view. You create and modify a navigation form.
Finally, you build and test an AutoExec macro to open the navigation form when the database opens.
[Student Learning Outcomes: 5.2, 5.3, 5.4, 5.5, 5.7, 6.4, 6.5, 6.7, 7.7]
Files Needed: MagnificentMeals‐03.accdb Completed Project File Names: [your initials] Access ACP 3.accdb
Skills Covered in this Project
Determine the need for a many‐to‐many relationship.
Create a junction table with a concatenated primary key.
Define a relationship with the Lookup Wizard. View and edit relationship properties. Use multiple columns in a lookup field. Add data into a junction table. Add a parameter to a report.
Add a subreport to a main report. Edit a report in Design view. Edit properties of a control in a report. Create a navigation form. Edit a form in Design view. Use the Macro Builder. Build a stand‐alone AutoExec macro. Test a macro.
1. Open the MagnificentMeals‐03.accdb database file from your student data files and save it as [your initials] Access ACP 3.
2. Enable content in the database if prompted. 3. Review the existing tables.
a. Open the Meals table in Datasheet view. Note that this table contains 11 records showing the individual meals that are offered for sale.
b. Switch to Design view. c. Note that the Primary Key of MenuID is a Number Data Type, with a Field Size of Long Integer. d. Open the Orders table in Datasheet view. Note that this table contains 12 records showing the
details about each order placed by a customer.
e. Switch to Design view. f. Note that the Primary Key of OrderID is a Short Text Data Type, with a Field Size of 5.
4. Determine the relationship type. a. A Meal could be ordered on many different orders. b. An Order could have many different meals. c. There is a many‐to‐many relationship between Meals and Orders. d. Close both tables.
5. Create a junction table. a. Click the Table Design button to create a new table in Design view. b. Enter the table fields using the information in the following table.
Access 2016: In Practice Page 2 of 6 Access Application Capstone Project 3
Figure 1 Access ACP 3, Magnificent Meals database relationships
c. Designate the MenuID‐FK and the OrderID‐FK fields as the concatenated primary key d. Save the table as MealOrder.
6. Use the Lookup Wizard to create a one‐to‐many relationship between the MealOrder and Orders tables.
a. Select Lookup Wizard in the Data Type property box for the OrderID‐FK field to create a relationship between the MealOrder and Orders tables.
b. Include the OrderID field in the Selected Fields: window. c. Do not sort by any fields. d. Enable data integrity and restrict deletes. e. Save and close the table to ensure that the changes are saved correctly.
7. Use the Lookup Wizard to create a one‐to‐many relationship between the MealOrder and Meals tables. This lookup will display multiple fields.
a. Open the MealOrder table in Design view. b. Select Lookup Wizard in the Data Type property box for the MenuID‐FK field to create a
relationship between the MealOrder and Meals tables.
c. Include the MenuID and MealName fields in the Selected Fields: window. d. Sort by the MealName field in ascending order. e. Do not hide the key column. f. Double‐click the right edge of the MealName column to adjust the width of the lookup column. g. Store the MenuID field. h. Enable data integrity and restrict deletes. i. Save and close the table.
8. Edit the relationships. a. Click the Relationships button to open the
Relationships window.
b. Click the Show Table button to add the MealOrder table into the window. Move the
tables so that the Relationships window is
similar to Figure 1.
c. Edit the relationship between Meals and MealOrder to cascade updates.
Field Name Data Type Description Field Size
OrderID-FK Short Text Must match an OrderID in the Orders table
5
MenuID-FK Number Must match a MenuID in the Meals table
Long Integer
3-ServingQty Number Number of 3-Serving portions ordered
Byte
6-ServingQty Number Number of 6-Serving portions ordered
Byte
Access 2016: In Practice Page 3 of 6 Access Application Capstone Project 3
d. Edit the relationship between Orders and MealOrder to cascade updates. e. Save the changes. f. Close the Relationships window.
9. Add the data in the following table into the MealOrder junction table. The table shows the values in
the additional lookup field to aid in your data entry.
10. Save and close the table. 11. Edit the OrderDetails report to add a parameter.
a. Open the OrderDetails report in Design view and open the Property Sheet. b. Ensure that the Selection type is set to Report. c. Open the Query Builder in the Record Source property. d. Click Yes when prompted to create a query based on the table. e. Add all five fields into the query grid.
OrderID‐FK MenuID‐FK MealName 3‐Serving
Qty
6‐Serving
Qty
10100 1 Savory Grilled Chicken with Sesame Honey Butter 0 1
10100 8 Chocolate Decadence Cake 0 1
10101 8 Chocolate Decadence Cake 0 1
10102 2 Cheese Stuffed Turkey Burger on a Pretzel Bun 1 0
10103 3 Southwest Pork Carnitas Wraps 1 0
10103 4 Chipotle Maple Corn 1 0
10103 9 Peanut Butter Cupcake 1 0
10104 7 Seafood Cioppino 0 2
10105 9 Peanut Butter Cupcake 0 4
10106 7 Seafood Cioppino 1 0
10107 2 Cheese Stuffed Turkey Burger on a Pretzel Bun 1 0
10107 10 Almond Green Beans 1 0
10107 11 Oatmeal Raisin Cookies 0 1
10108 8 Chocolate Decadence Cake 0 2
10109 7 Seafood Cioppino 1 1
10110 1 Savory Grilled Chicken with Sesame Honey Butter 1 0
10110 10 Almond Green Beans 1 0
10111 1 Savory Grilled Chicken with Sesame Honey Butter 1 1
Access 2016: In Practice Page 4 of 6 Access Application Capstone Project 3
f. Create a flexible parameter so users can leave the parameter blank if they wish to see all of the orders. Enter the following text into the Criteria box of the DeliveryDateTime field: Like "*" & [Enter the delivery date to search for (Leave blank to search for all delivery dates)] & "*"
g. Save the query and close the Query Builder. h. Save the report. i. Switch to Report view to test the changes made to the report. j. Enter 6/5/2018 in the Enter Parameter Value dialog box and press OK. The report should
show the two orders scheduled for delivery on that date.
12. Add a subreport to the OrderDetails main report. Refer back to How To Use a Table or Query as a Subreport in a Main Report in SLO 6.7 Using Subreports if you need additional guidance.
a. Change the OrderDetails report to Design view. b. Verify the Use Control Wizards button is turned on then use the Subform/Subreport button to
add a subreport onto the main report. Position the subreport below the existing fields near the
left side of the main report.
c. Verify that the Use existing Tables and Queries button is selected on the first page of the SubReport wizard.
d. Choose the MealOrder table and add the MenuID‐FK field from the MealOrder table onto the subreport on the second page of the SubReport wizard.
e. Change the table selection to the Meals table and add the MealName field onto the subreport. f. Change the table selection back to the MealOrder table and add the 3‐ServingQTY and 6‐
ServingQTY fields onto the subreport.
g. Accept the Choose from a list radio button and the default linking statement on the third page of the SubReport wizard.
h. Edit the suggested name for the subreport so that it reads MealOrderSubreport on the last page of the wizard.
i. Use Layout view to verify that the subreport is correctly linked to the main report. Leave the Enter Parameter
Dialog box blank
and press OK when
prompted. The
report should look
similar to Figure 2.
j. Save and close the report. Save any
changes if
prompted.
13. Edit the MealOrderSubreport in Design view. a. Open the MealOrderSubreport in Design view. Open the Property Sheet. b. Select the MenuID‐FK combo box. Enter .35” in the Width property and .2” in the Left
property on the Format tab. Select Transparent in the Border Style property and Center in the
Text Align property.
c. Select the MenuID‐FK label. Enter Menu ID in the Caption property, .6” in the Width property, and .05” in the Left property on the Format tab.
Figure 2 Access ACP 3, Layout view after subreport is added to main report
Access 2016: In Practice Page 5 of 6 Access Application Capstone Project 3
Figure 3 Access ACP 3, Completed OrderDetails report in Report view
d. Select the MealName text box. Enter .8” in the Left property on the Format tab. e. Select the MealName label. Enter Meal Name in the Caption property and .8” in the Left
property on the Format tab.
f. Select the 3‐ServingQTY label. Enter 3-Serving QTY in the Caption property, .94” in the Width property, and 5.1” in the Left property on the Format tab.
g. Select the 3‐ServingQTY text box. Enter .3” in the Width property and 5.4” in the Left property on the Format tab. Select Center in the Text Align property.
h. Select the 6‐ServingQTY label. Enter 6-Serving QTY in the Caption property, .94” in the Width property, and 6.2” in the Left property on the Format tab.
i. Select the 6‐ServingQTY text box. Enter.3” in the Width property and 6.5” in the Left property on the Format tab. Select Center in the Text Align property.
j. Adjust the Width property of the Report to be 7.5”. k. Adjust the Height property of the Detail section to be .3”. l. Adjust the Height property of the ReportHeader section to be .3”. m. Save and close the subreport.
14. Enhance the OrderDetails report and test it. a. Open the OrderDetails report in Design view. Open the Property Sheet. b. Adjust the Width property of the report to be 7.5”. c. Adjust the Height property of the
Detail section to be 2”. d. Select the MealOrderSubreport
subform/subreport control. Select
Transparent in the Border Style
property on the Format tab.
e. Delete the MealOrderSubreport label.
f. Save the report. g. Change to Report view to test the
report. Enter 6/5/2018 in the Enter Parameter Value dialog box and press OK when prompted. The report should show the two orders scheduled for delivery on that date (Figure 3).
h. Close the report. 15. Create a navigation form.
a. Create a new form using the Navigation button. b. Select the Vertical Tabs, Left form style from the list of choices. c. Drag the MealListings form to the [Add New] button. d. Drag the OrderDetails report to the [Add New] button. If the Enter Parameter Value dialog box
opens, press Cancel.
e. Save the form as MainMenu. 16. Enhance the look of the MainMenu form.
a. Change to Design view. Open the Property Sheet.
Access 2016: In Practice Page 6 of 6 Access Application Capstone Project 3
b. Edit the text of the MealListings tab so there is a space between the words (Meal Listings) and select Bold in the Font Weight property.
c. Edit the text of the OrderDetails tab to read Order Report. d. Edit the text of the Navigation Form label to read Magnificent Meals. e. Delete the icon in the Form Header. f. Select the Form Header section bar and enter #FCE6D4 in the Back Color property. g. Select the Meal Listings and Order Details tabs.
Enter #FCE6D4 in the Back Color property Select Solid in the Border Style property. Enter #F7B580 in the Pressed Color property. Enter #000000 in the Pressed Fore Color property. Enter #595959 in the Fore Color property.
h. Save and close the form. 17. Test the form.
a. Double‐click to open the MainMenu form. The form shows the two
navigation tabs; the Meal Listings
tab is selected (Figure 4).
b. Click the Order Report tab. Leave the Enter Parameter Dialog box
blank and press OK when
prompted. The report displays.
c. Close the form. 18. Create a stand‐alone macro to open the
MainMenu form when the database
opens.
a. Click the Macro button to open the Macro Builder.
b. Enter the macro code shown in Figure 5. c. Save the macro as AutoExec. d. Close the macro.
19. Test the macro. a. Close Access. b. Open the [your initials] Access ACP 3.accdb database file. The
database starts and opens the MainMenu form in Form view.
20. Close the database.
Figure 5 Access ACP 3, Completed AutoExec macro
Figure 4 Access ACP 3, Completed MainMenu navigation form
