Micrsoft Access help

In this hands-on project, you will be downloading a database from the West Side Restaurant Group, the management company for several high-quality restaurants. You will be completing the following tasks in Microsoft Access:

· Create simple and advanced queries

· Modify a query

· Sort records in a query

· Working with different data types

· Creating expressions

Project Preparation

1. Download the  Lesson 11_Hands On Project 1(opens in a new tab) file to your course lesson folder.

2. Extract the file to the same folder. 

3. Open the file in Microsoft Access.

4. Save your file as " Lesson11_Project1_MEID.accdb" where MEID is your MEID.

Project Directions

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