Microsoft Access

Access 2016: In Practice Page 1 of 5 Access Application Capstone Project 1 Last Revised 9/12/16

ACCESS 2016: IN PRACTICE—APPLICATION CAPSTONE PROJECT

Access 2016: Application Capstone Project 1 (Chapters 1-4)

For this project, you enhance the functionality of a database for Magnificent Meals, a home-cooked

meal preparation and delivery company. You edit properties in the database table and import data from

Excel. You create a form using the Form Wizard and improve the functionality by modifying the layout.

Finally, you create a query and add a parameter to that query.

[Student Learning Outcomes: 1.2, 1.4, 1.5, 2.1, 2.2, 2.4, 3.2, 3.4, 3.6, 4.1, 4.2, 4.3]

Files Needed: MagnificentMeals.accdb and MenuData.xlsx Completed Project File Names: [your initials] Access ACP 1.accdb

Skills Covered in this Project

 Add database properties.

 Change the datasheet layout.

 Open, save, and close, a table.

 Edit a table using Design view.

 Edit field properties in Design view.

 Create a lookup field.

 Import records from Excel.

 Create a query in Design view.

 Execute a query.

 Sort query results

 Add a parameter to a query.

 Create a form using the Form Wizard.

 Edit a form in Layout view.

 Move fields within the control layout.

 Set form properties to limit user edits and deletes.

 Test the functionality of a form.

1. Open the MagnificentMeals.accdb database file from your student data files and save it as [your

initials] Access ACP 1.

2. Open the Meals table in Datasheet view. Verify that the table does not contain any records.

a. Switch to Design view to edit field properties. b. Change the Data Type of the DateAddedToMenu field to Date/Time. c. Select a Short Date Format for the DateAddedToMenu field. d. Make all fields Required. e. Save the table.

3. Create a lookup field using the Lookup Wizard. a. Use the Lookup Wizard to create a lookup field for the MealType field. b. Type the following values as list choices:

Main Course Side Dish Soup Dessert

c. Don’t change the label for the lookup field; it should be MealType. d. Limit the choices to those on the list. e. Finish the Lookup Wizard. f. Enter 11 in the Field Size of the MealType field. g. Save and close the table.

4. Import data from Excel into the Meals table. a. Launch the Get External Data–Excel Spreadsheet dialog box. b. Locate and select the MenuData.xlsx Excel file. c. Append the records to the Meals table. d. Click OK to launch the Import Spreadsheet Wizard. e. Finish the Import Spreadsheet Wizard. Close the Wizard after the records have imported.

Access 2016: In Practice Page 2 of 5 Access Application Capstone Project 1 Last Revised 9/12/16

f. Open the Meals table in Datasheet view. Your table should contain 10 records (Figure 1).

5. Change the layout of the Meals table. a. Change the Font Size to 12 pt. b. Center-align the MenuID field. c. Use AutoFit to adjust the MealName

field to the correct size. d. Save and close the table.

6. Create a form using the Form Wizard. a. Add all the fields from the Meals table

to the form. b. Choose a Columnar layout. c. Type MealListings in the Title

box. d. Click the Finish button. The form

opens in Form view (Figure 2). 7. Edit the form in Layout view.

a. Switch to Layout view. If the Field List or Property Sheet open, close them.

b. Click the Label button [Form Layout Tools Design tab, Controls group] and then click to the right of the MealType text box. A new label is added to the form, but it is added on top of the current MenuID label.

c. Type Nutrition Information Per Serving into the label and press Enter. d. Apply Bold formatting to the Nutrition Information Per Serving label. e. Use the arrow keys to move this label down and to the right of the MealType combo box. The

exact placement is not critical at this point. f. Edit the MenuID, MealName, MealType, 3-ServingCost, 6-ServingCost, ServingSize, TotalFat,

Sodium, Cholesterol, TotalCarbs, Protein, and DateAddedToMenu labels to match the labels shown in Figure 3. The edits add spaces to separate the words in the label caption and/or add a measurement value to the labels related to the nutrition information.

g. Save the form. Remember it is a good idea to periodically save your work after having made a number of changes.

h. Move the Date Added To Menu label and text box to the right of the Menu ID label and textbox (Figure 3). Remember that you can select multiple controls by pressing and holding the Shift key when selecting the controls. This allows you to more quickly make the same change to multiple controls.

Figure 1 Access ACP 1, Meals table after impoting external data

Figure 2 Access ACP 1, MealListings form after completing the Form Wizard

Access 2016: In Practice Page 3 of 5 Access Application Capstone Project 1 Last Revised 9/12/16

i. Adjust the size and location of the labels and text boxes on the form so they are similar to the layout shown in Figure 2. Some of the controls may need to be moved to a temporary location before you move them to the final location.

j. Edit the label content, width, and location of the MealListings label in the header section. k. Save the form.

8. Edit the form properties to limit user edits and deletes. a. Open the Property Sheet of the form. b. Select Form in the Selection type. c. Set the Data Entry, Allow Deletions, and Allow Edits properties to No. d. Set the Allow Additions property to Yes. e. Close the Property Sheet. f. Save the form.

9. Test the form. a. Switch to Form view. b. Enter the following data:

Figure 3 Access ACP 1, completed form design

Access 2016: In Practice Page 4 of 5 Access Application Capstone Project 1 Last Revised 9/12/16

Menu ID 11

Meal Name Oatmeal Raisin Cookies

Description Soft, chewy oatmeal and raisin cookies are flavored with just the right amount

of brown sugar and cinnamon.

Meal Type Dessert

3-Serving Cost 5.84

6-Serving Cost 10.00

Serving Size 2 cookies

Calories 150

Total Fat 66

Sodium 182

Cholesterol 29

Total Carbs 25

Protein 2

Data Added to Menu 5/1/2018

c. Use the navigation buttons to move back to the first record. d. Notice that the Delete button on the Home tab is not enabled. e. Click in the Meal Name text box. Try to type in that field. Access should not allow you to make

any edits. f. Close the form.

10. Create a query and sort the query results. a. Create a new query in Design view. b. Add the Meals table to the query and add the following fields into the query: MenuID,

MealName, MealType, ServingSize, and Calories. c. Sort the query by the Calories field in ascending order. d. Save the query as MealsByType. e. Run the query to confirm it displays the 11 records in ascending order by the Calories field.

11. Edit the query to add a parameter that allows the user to enter the meal type when the query runs. a. Switch back to Design view. b. Type [Enter the Meal Type] as the criterion for the MealType field. c. Save the query. d. Test the query to verify that it works. Run the query. e. Type Main Course in the Enter Parameter Value dialog box, and click OK. f. Verify that the datasheet shows the four records that meet the criteria (Figure 4). g. Close the query.

Figure 4 Access ACP 1, Query results when MealType parameter is used

Access 2016: In Practice Page 5 of 5 Access Application Capstone Project 1 Last Revised 9/12/16

12. Add the following database properties. a. Title: Magnificent Meals Product Database b. Author: Student Name c. Company: Magnificent Meals d. Click OK to save the updated properties.

13. Close the database.