Micrsoft Access help

In this hands-on project, you will be using your database file from the completion of Lesson 11 Project 1. You will be serving as a manager for the West Side Restaurant Group and completing the following tasks in Microsoft Access:

· Creating a database form from an Access table

· Resizing and arranging form fields

· Adding a combo box to a form 

· Creating a database report from an Access query

· Resizing and arranging report fields

· Adding a logo, theme, color scheme and font selection to forms and reports

Project Preparation

1. For this lesson, you will begin with  your ending file from Lesson 11, Project 1:  Lesson11_Project1_MEID.accdb" where MEID is your MEID

2. Re-save the database as  Lesson11_Project2_MEID.accdb" where MEID is your MEID

3. Download the  West Side Logo(opens in a new tab) image file to your course lesson folder.  (Note: After clicking the link, you may need to right-click on the image file and select "Save Image As" to save it to your lesson folder.)

Part 1: Create a Form

1. Create a Form using the  Form Wizard from the  Orders Table.

2. Add the following  fields to the form: ID, Item, Restaurant, Restaurant ID, Cost, and Quantity.

3. Use the  Columnar Layout. 

4. Name your form "Supply Order Form". 

5. Select the radio button to Modify the form's design and click  Finish.

6. Use the wizard to  add a Combo Box

· Insert the field above the  Item textbox in the open space to the right of the ID.

· Type the values for the combo box. There should only be two options: CMF Restaurant Supply and American Supply Warehouse.

· Store the value from this Combo Box in the  Vendor field.

· Name the Combo Box  Vendor.

7. Add the date and time to the  form header using a format of your choice.

8. Move the title field, which displays "Supply Order Form" so the left boundary is at the 1.5in mark of the horizontal ruler. Move the right boundary of the title field to the 4in mark of the horizontal ruler.

9. Add the West Side Restaurant Group  logo to the form. Resize and expand the image as necessary to see all the logo details.

10. Modify the fields on the form so that all are readable and appropriately sized to the information. Ensure no fields display "#######" which indicates the field is too small for the data.

11. Add a new record using your form for Pizzeria Italia for QTY: 1- Pizza Oven from CMF Restaurant Supply for $9,489.00. Ensure you enter in the correct ID for the Pizzeria Italia Restaurant. (See illustration below.)

12. Select the  Wisp Theme.

13. Select the  Violet II  color scheme option

14. Select  Century Gothic font. 

15. Save and close the Supply Order Form. 

Supply Order Form

Part 2: Create a Report

1. Use the  Report Wizard to create a  report from the  Orders Table using all available fields. 

· Group by  Restaurant. (Make sure that you are only grouping by Restaurant.)

· Sort Ascending by  Cost. 

· Select the  Outline  Layout with  Landscape orientation.

· Name the report " Placed Orders by Cost"

· Preview your report. 

2. Enter Design mode for the report and make the following modifications

· Remove the field ID

· Remove the field Restaurant ID

· Arrange the fields so they appear in the following order from left to right:  Item, Vendor, Quantity, Cost, Purchased Date. (Hint: Remember that you can hold down the CTRL key to select multiple fields and labels, and then move them together as one.)

· Resize the fields appropriately so that data is visible. Preview the report and ensure no fields display "#######" and all text displays.

3. Insert the West Side Restaurant Group logo into the report, as you did with the form, move the Report title so that the two do not overlap.

4. Apply the  Integral  theme. 

5. Apply the  Blue Green color scheme.

6. Apply  Arial font.

7. Preview your report. Make any necessary changes so that everything displays. The report will likely be on 2 pages. This is ok!

8. Save and close your report.  

Placed Orders by Cost Report - Grouped by Restaurant

Lesson 11 Hands on Project 1

Part 1: Ryan's Bistro Orders Query

1. Using the Query Design command, create a query from the Orders Table.

2. Add the  Orders Table to the Query Design pane. 

3. Add the following fields to the Query Design grid:  ID, Restaurant, Item, Cost, and  Quantity.

4. In the  Criteria cell under the Restaurant field name type " Ryan's Bistro".

5. Run the query. Verify your results - only records for Ryan's Bistro should show up in the results.

6. Add a calculated field to the  Ryan's Bistro query to calculate the extended cost for each record.  (In accounting, an extended cost is the unit cost multiplied by the number of those items that were purchased.)  You may create the expression manually or use the expression builder. 

7. Format the  Extended Cost column as  Currency.  

8. Sort the query in Ascending order by Extended Cost.

9. Run the query and verify the results. Make sure the Extended Cost values appear in ascending order in currency format. There should be 11 records.

10. Close and Save the query.  Name your query " Ryan's Bistro Orders".

Part 2: CMF Supply Orders in AZ Query

1. Create a new Table, call "Restaurants" with the following fields: 

· Restaurant - Short Text

· Street Address - Short Text

· City - Short Text

· State - Short Text

· Zip Code - Short Text

· Change the name of the ID field to "Restaurant ID".

2. Save the table and use the Datasheet View to fill in the following information:

Restaurant	Street Address	City	State	Zip Code
Ryan's Bistro	567 Drake Rd.	Tempe	AZ	85282
Café 23	38347 Wesker Ave.	Mesa	AZ	85212
Pizzaria Italia	2290 Atomic Blvd.	San Diego	CA	94321

3. Edit the  Orders Table:

· Add a new field called "Restaurant ID"

· Set the Type to Number

· Use the Datasheet view to enter the following IDs for each Order:

· Ryan's Bistro: 1

· Café 23: 2

· Pizzaria Italia: 3 NOTE: Before entering the Restaurant IDs into the Orders Table, verify that these are the actual Restaurant IDs in the Restaurants table. If you created any extra records when inputting your Restaurant data, your IDs may be different since Access automatically assigns these numbers.

4. Create a  relationship between the  Orders Table and the  Restaurants table.

· Use the Restaurant ID field in the Restaurants Table joined to the Restaurant ID field in the Orders Table.

· This should create a One-to-Many relationship.

· Enforce Referential Integrity in this relationship.

5. Create a new  Query from the Orders Table

· Add both the Orders Table and Restaurants table

· Add in the following fields from Orders Table: Restaurant, Item, Vendor, Date Purchased

· Add in the following fields from the Restaurant table: City, State

6. Run the Query, there should be 21 records.

7. Save your Query as "CMF Supply Orders in AZ"

8. Edit the Query Design

· Set  Criteria for State to be "AZ"

· Set  Criteria for Vendor to be "CMF Restaurant Supply"

9. Run the Query, there should be 13 records.

10. Save and close the query

11. Save, compact, and repair your database.

12. Move on to Project 02.

 

Ryan's Bistro Query

CMF Supply Orders in AZ Query