Homework answers / question archive / MIS310 Access Individual Project   Your name_________________________________________________________________________________ Please complete following steps and submit your final Access Database file and this sheet to CI Learn by due date: ____1

MIS310 Access Individual Project   Your name_________________________________________________________________________________ Please complete following steps and submit your final Access Database file and this sheet to CI Learn by due date: ____1

MS Access

Share With

---

MIS310 Access Individual Project

 

Your name_________________________________________________________________________________

Please complete following steps and submit your final Access Database file and this sheet to CI Learn by due date:

____1.                  Transfer the file named TestNorthwind.accdb from CI Learn to your own computer or Network drive: U: Drive. Rename it as YourLastName_FirstName_Access_Project.accdb. Open the file.

____2.                  Open the relationships window and create a relationship between the Products and Order Details table using the Product ID field.  Enforce Referential integrity and cascades.

____3.                  Open the employee’s table and replace Andrew Fuller’s name with your own.  You do not need to fill in the address, etc.

____4.                  Open the Category table, create a new field, Rate, and enter the following values.  Hint: A percent is NOT an integer.

Category Name Rate

Beverages           4.00%

Condiments       7.00%

Confections        10.00%

Dairy Products   5.00%

Grains/Cereals  3.50%

Meat/Poultry    8.00%

Produce               10.00%

Seafood               6.00%

____5.                  Create a query that uses the following fields.  Name the query with your name.

CategoryName

Country(Customer Table)

LastName (Employee Table)

Quantity

Rate       Discount

ShippedDate

ProductName

UnitPrice

____6.                  Calculate a new field in above created query, name it Revenue = UnitPrice * (1-Discount) * Quantity.

____7.                  Limit the query to records of orders shipped in 1998.  Name your query 1998.

____8.                  From your 1998 query, answer the following questions.  Write your answers in the blanks.  Name each of following query starting from query 1, query 2, …, query 5 and save them separately in your access file.

____9.                            ___________ How many orders in 1998 were made by customers in the USA?

____10.               ___________How many 1998 records generated revenue between $50 and $100? 

____11.               ___________How many 1998 records were for Ikura orders? 

____12.               ___________How many 1998 records were for Seafood orders shipped in April? 

____13.               ___________How many 1998 records were for Produce or Seafood? 

____14.               From Your Name query, calculate commission (insert a new calculated field).  Multiply Revenue times Rate. Save it as Commission Query.

____15.               Prepare a report showing only YOUR sales in 1998 by Category and Month.  The report should only show the Category name, Revenue, Commission and the month that the order was shipped.  THIS SHOULD BE A SUMMARY REPORT WITH APPROPRIATE GROUPINGS.

____16.               Place a footer on each page of the report that tells me "This report prepared by Your Name and today’s date."

____17.               Alter the layout of the report so that all field names display and all totals are in currency format.  Align decimal places.

 

 

 

TURN IN:

 

1.            Your completed YourLastName_FirstName_Access_Project.accdb to CI Learn submission Folder.

2.            This sheet with your name and answers for questions 9-13 to CI Learn submission Folder.