the business description on the following page and complete

MICROSOFT ACCESS NEEDED FOR THIS ASSIGNMENT ACCESS DOC IS ATTACHED

ACCESS ASSESSMENT ASSIGNMENT

Please read the business description on the following page and complete the 12 actions listed. This is an individual assignment. YOU MAY NOT work with any other students on this assignment. Students violating this policy will receive a zero on the assignment and potentially an F in the course. You must submit a statement to this effect with your database (see requirement #1 below).

You should submit your Microsoft Access file on Blackboard using the digital drop-box link provided by midnight on Thursday, March 31, 2016.

Business Description and Assignment:

DineatHome.com in Naples, Florida is an online service that lets people order meals from one or more area restaurants for home delivery. Claire Picard, the founder and owner, picks up and delivers meals as ordered by customers. Claire currently has all of her information in an Excel spreadsheet. The spreadsheet contains information about the company’s customers, including their first name, last name, and phone number, the order date for their orders, the order amount for each order, and the customer’s preference for where their meal is delivered (at their home, school, or work). Claire also tracks information about restaurants with whom she does business, including the name of the restaurant, the address (street, city, state, zip), phone number, contact name (first and last name), whether or not they have a web site, whether or not they are takeout only, and the minimum dollar order.

Open the database and perform the following actions.

1)Under the file tab, open the “View and edit database properties” on the far right. Put your name in the Manager field and in the comments area, enter and attest to the following:

I certify that I have not received help from any unauthorized source, including people inside and outside the class. I understand that violation of the NIU Honor Code will be cause for an honor code violation that could result in disciplinary action in the course.

2)Prior to establishing relationships between the tables in the database, set up a combo box (which is a drop-down box established through the look-up wizard in the data type field in design view) for each foreign key column to look up values from its corresponding primary key column. Be sure to make the drop-down meaningful (i.e. I won’t have memorized each company or employee ID number).

3)Edit the relationships between the Restaurant, Order, and Customer tables to enforce referential integrity.

4)Set up a domain validation rule for “Title of Courtesy” such that it allows only values such as “Mr.”, “Mrs.”, “Dr.”, “Miss” and “Ms.” Hint: this rule is added to the field properties in design view.

5)Determine the customers that have placed orders larger than $125. Include the first and last name of customer, the name of the restaurant, the city the restaurant is in, the order amount in descending order, and the order date. Save this query as “Large Orders.”

6)List all restaurants that do not have websites. Include the name of the restaurant, the city, the owner’s first and last name, and the phone number of the restaurant. Do not display the website field. Save this query as “Restaurants without Websites.”

7)Design a parameter query that lists all fields in the restaurant table but prompts the user for the zip code when the query is run. The text of the prompt should be “Enter Zip Code.” Name this query “Restaurant Zip Code Parameter.” Hint: this is called a parameter query.

8)Use a query to list orders for restaurants #104 and 108. Include the restaurant #, the restaurant name and the order amount. Sort the restaurant name field from in alphabetical order. Save this query as “Selected Marco Island Restaurants.”

9)Design a query that shows all customers and their orders. Sort in ascending order by Customer ID. Show the following fields in the query: Customer ID (from Customer table only), customers’ first and last name and phone number, the customer’s preference for delivery site, Order #, restaurant #, order date and order amount. Set the criteria so that only phone numbers that start with 957 will show when the query is run (i.e. use the “like” operator). Save this query as “957 Customers and Orders.”

10)Design a query that will show all customers who have not placed any orders. Include all fields from the customer table. Save this query as “Customers with no orders.” Hint: use the “find unmatched query” wizard.

11)Design a query that will show customers that have placed more than one order. Include all fields from the Order table. Save this query as “Customers with multiple orders.” Hint: use the “find duplicates query” wizard.

12)Design a query that will show all customers and their orders. Modify the query so that it shows only (i.e. “returns” in design view) the 5 highest order dollar amount. Show all fields in the Customer and Order tables. Save this query as “High Dollar Orders.”

13)Design a query that will show customers who only get home delivery and who have placed orders more than $50. Show all fields. Save this query as “Home Delivery and More Than $50.”