MS Access Homework help

Microsoft Access Exercise

In this assignment, you will design a relational database for a business that sells high-quality frozen meals over the Internet.

Don't use plagiarized sources. Get Your Custom Essay on

This is a Snippet Preview, get a Complete Solution Here

Order Essay

After your database design is completed, you will create database tables and populate them with data. Then you will produce one form, three queries and one report.

Note that the screenshot images in the instructions may appear somewhat different than what you see in your database, depending upon what database version you are using. Nonetheless, the same functions/actions will be available. A little research should provide you the information you need.

If Microsoft Access is not installed on your computer, then you can use the software in the Cook library computer lab/CBE Computer Lab. Title your database according to the rules used for the course

Your dad’s friends, Fred and David plan to sell quality, gourmet frozen meals over the Internet. He hires you for the summer based on your proficiency in database design and Access to help create this exciting business.

Fred envisions the business to include the following tables: Customers, meals, orders, shipping costs, and transactions.

The next slide would show you the steps to create tables in Access

Microsoft Access Exercise

NOTE: screenshots may appear slightly different depending upon MS Access version you are using (These steps also apply to MS Access 2013)

Open Microsoft Access to create database, then

1) Click Blank desktop database

2) Name database: DB1_Yourlastname__Yourfirstname.accdb

Click

Create Tables

The database will open in DataSheet view (Images on next pg.)

Click on view button and click Design View (3)

Create 5 Tables in Design View. Create a first table named Customers (4) with the following fields

CustomerID ‐ number/primary key (5)

Firstname ‐ text

Lastname ‐ text

Address – text

City‐text

State-text

Zipcode-text

Email-text

Telephone#-text

CreditCard#-text

Do not add data to the tables yet

Create a table http://www.youtube.com/watch?v=MhfNQci_VzU

Click

Create Tables (continued)

Create a second table named Meals with the

following fields

– MealID ‐ number/primary key

– MealName ‐ text

– Price ‐ currency

– Mealdescription ‐ text

• Create a third table name Transaction with the following

fields

– OrderID ‐ number/primary key

– MealID – number/primary key

– Quantities ‐ number

Create Tables (continued)

Create a fourth table named Shipping with the following fields

– ShippingOvernight ‐ Yes/No

– Price ‐ currency

• Create a fifth table name Orders with the following

fields

– OrderID ‐ number/primary key

– CustomerID – number

– ShippingDate ‐ Date/Time

– ShippingOvernight – Yes/No

Table Relationships

Create table relationships see (numbered figures on next page)

Click the tab “Database Tools” and then click “Relationships”

(1) Add all 5 tables

(2,3,4) Relate Customers table to Orders table via Customers table primary key by clicking on Customers:CustomerID and dragging the cursor to Orders:CustomerID

(2,3,4) Relate Transaction table to Orders table via Orders table primary key by clicking on Orders:OrderID and dragging the cursor to Transaction:OrderID

(2,3,4) Relate Meals table to Transaction table via Meals table primary key by clicking on Meals:MealID and dragging the cursor to Transaction:MealID

(2,3,4) Relate Shipping table to Orders table via Shipping table primary key by clicking on Shipping:ShippingOvernight and dragging the cursor to Orders:ShippingOvernight

(2,3,4) Enforce referential integrity, cascade updates, cascade deletes by double clicking or right clicking on the relationship line)

Resulting relationships should look like (5) on next page

Create relationships https://www.youtube.com/watch?v=HYf9OZQH4hQ

Step 5

1) Note: If you get an error message “Cannot enforce referential integrity in the tables”

Solution: First close all tabs in the database. When you are entering data in the Orders table make sure that values in fields, CustomerID and ShippingOvernight fields correspondto same values in the CustomerID field in Customers table and ShippingOvernight field in the Shipping table. These two fields are foreign keys in the Orders table.

2) If you get an error message “Database is locked”

Solution – Close all tabs and redo the step

Add data to tables

– Double click Customers table in the left pane

– Add data to the table, add any data you like.

– Add data to Customers, Shipping, Meals,

Transaction, Orders in that order

• Try and note what occurs:

– Try and add text (not numbers) to primary key (which is a numbers field)

– Try and add data to Orders table that is not in Customers table or Transaction table or Shipping table

– Try and add same MealID to multiple records in Meal table

– Try and delete record in Orders table that is also in Transaction table

Note

Note: If you get an error message “Cannot enter data in a particular table”

Solution: First, close all tabs in the database. Make sure that the data entered is following foreign key constraints. For example, if you enter a CustomerID number (For eg, 17) in Orders table, make sure the same number (17) is available in the CustomerID column in the Customers table. This applies to all foreign key relationships in the database.

Create data input forms

– Click “Create” tab, and then click “Form Wizard”

– Create input form (for Orders table)

– Work through the wizards and add fields to forms (1)

– Use Design View on the Orders Form and manually modify form design as you see fit (2)

Create a form https://www.youtube.com/watch?v=GXML5L1OaZI

Creating Queries

Create three queries:

Query 1

Create a query called Customer order details which shows a list of all orders placed by a specific customer (First and last name). The query should show the customer’s First name, Last name, CustomerID and Shipping Date.

Creating Queries

Click on the create tab and then select “Query Wizard”

Creating Queries

The new query dialog box will open

Creating Queries

Click OK, the simple query dialog box will open

Click on the field you want to display in your query, then use the arrow to move the field to the selected field section

Creating Queries

From the table/query section you will be able to display the table(s) that you need for your query by clicking on the arrow

Creating Queries

From the customer’s table, select customer first name, last name, and customer ID fields and place them in the selected field section using the arrow

Creating Queries

Click on the table/query section of the simple query dialog box to select the shipping table

Creating Queries

From the order table select shipping date

Creating Queries

Click next and select detail (shows every field of every record)

Creating Queries

Click next and enter the title of your query in the section where it says “what title do you want for your query”

Creating Queries

Type “Customer order details”

Creating Queries

Click finish the query is created

Creating Queries

In the same manner create the following queries:

Query 2

Create a query called shipping cost for specific order. Show the list of all orders by each customer ID, First Name, Last Name, order ID and Shipping Price

Query 3

Create a query called ‘food type’ for each customer where you show customer information including Customer ID, First Name, Last Name, Order ID, Meal ID and Meal Name

Creating Reports

Click “Create” tab, and then click “Report Wizard” (1)

From the customer table add Customer ID (2)

Create a report http://www.youtube.com/watch?v=tKQLpq13mkM

Creating Reports

Work through the wizards and add fields to Report

From the order table add order_ID, shipping_date, and shipping_overnight

Use Design View on the Orders report and manually modify report design as you see fit

Finishing Up

The following YouTube links also provides the steps (applicable for MS Access 2010/13):

Create a table http://www.youtube.com/watch?v=MhfNQci_VzU

Create relationships https://www.youtube.com/watch?v=HYf9OZQH4hQ