Microsoft ACCESS

FINAL PROJECT (CIT125)

The Access Final Project is worth 30% of your grade.

For this assignment, you are to create a database for a store. Your database should include at least those three tables as described below.

Create a database called Store with the following tables:

“Suppliers” table: 3-5 people working in the virtual store, each person is represented by a table row. The table should include SupplierID, last and first name, title, phone number.

“Products” table that contains information about at least 10 products sold by the store. Each record in the table should include: (1) a “ProductID,” (2) the name of the product, (3) the retail price of a unit of the product, (4) the number of units of the product in stock, and (5) the product picture image. Assume that the only unique identifier of a product is its “ProductID.”

an “Orders” table that includes information about 8 orders of products. It should include: (1) an “OrderID,” (2) a “ProductID,” (3) the wholesale price of a unit of the product ordered, (4) the number of units of the product ordered, (5) the date that the product was ordered, (6) Supplier responsible for that order.

The following conditions should be met:

Each employee can process many orders, but each order can be processed by only one employee.

Each order can have many products, and each product can belong to many orders.

Give your database the following file name: “final.yourname.accdb.” Save the file.

Good Luck and have fun creating this database!

I. TABLES (read all of the following before you create your tables,
    30 points total)

Tables (10 points)

The “Products” table follows the specifications above.

The “Suppliers” table follows the specifications above.

The “Orders” table follows the specifications above.

Primary Keys (3 pts)

Primary key(s) are assigned appropriately in each table.

Relationships (4 pts)

Don’t forget to create relationships between the tables

 

Masks, Data Types and Field Sizes (10 pts)

The phone number, zip code, and two date fields in the above tables all have masks when you enter the value for the field. Provide appropriate field sizes.

All fields referring to money have the “currency” data type.

Default Value for Date (3pts)

For the Orders table, make the current date the default value for the date the product was ordered.

II. QUERIES, REPORTS, and FORMS

QUERIES (40 points total)

Note:	Save each of the following queries. Save query1 under the name “Query1,” query2 under the name “Query2,” etc. Also, all sorting should be “ascending.”
Query1:	Show the names of all suppliers and their states. Sort by the name of the supplier.
Query2:	Show the names of all suppliers who are in a certain state (e.g., “NC”). Sort by the name of the supplier.
Query3:	Show all of the fields in the Suppliers table for those suppliers who are in a certain city (e.g., “Charlotte”). Sort by name of the supplier. (make sure the city field and supplier name field do not appear twice in the answer.)
Query4:	Show the names of all products that begin with a given letter.
Query5:	Show the names of all products that appear alphabetically AND that have the number of units in stock greater than a certain number.
Query6:	Show the names of all products that have a retail price below a certain amount AND that have the number of units in stock greater than a certain number. Also show the unit retail price.
Query7:	Show the names of all products that have a price below a certain amount OR that have the number of units in stock greater than a certain number.
Query8:	Show the names of all products which names begin with either of two letters.
Query9:	Show the names of all of those products that have a price below a certain amount AND that have the number of units in stock greater than a certain number. Show those products whose names begin with a given letter. Show the unit retail price..
Query10:	Show a count of the number of suppliers in each state. Sort by state.

REPORT (15 points total)

Create a report that shows the name of each product in the store, the unit retail price of each product, and the number of units in stock. Sort by the name of the product. Use a tabular layout.

Change the title of the above report to make it more descriptive. Include the name of your store in the title.

At the end of the above report, include a count of the different kinds of products in the store (do not total the number of units in stock of all of the products). Give it the label “Number of Products.” Left-align this label with the left-most column in the report.

FORM (15 points total)

Create a form that can be used to enter suppliers data into the Suppliers table. Use the columnar layout.

Add a title to the Suppliers form in its header. (Make the font size sufficiently large.)

Include at least two command buttons in the header of the Suppliers form that perform useful actions. Make the action buttons the same size and align them.

General appearance of Forms

The general appearance of the form. Make sure items in a column of the form are aligned

 

III. GENERAL APPEARANCE OF THE PROJECT.

Page 1 – Cover sheet

• College Name
• Course Name
• Course Code
• Instructor Name
• Project Name
• Student Name and Section Number

Page 2

• Give a brief definition of the Microsoft Access 2010 software. Supply with a quote and a footnote for the reference source.
• Describe what the application is good for. Supply with a quote and a footnote for the reference source.
• In your opinion, how the skills acquired in the class will help you in your career.

Pages 3 to 5 – Assignment Description

• Provide the Description of the project

 

Pages 6 to 12 – Screenshots of each table design AND datasheet view

Page 13– Queries SQL View to be collected into the Microsoft Word Document. Please leave the tasks description for each query.

Page 14– Form

• Printout (or screenshot) of 1 page of the form

Page 15– Report

• Printout (or screenshot) of 1 page of the report