The Scenario
Your new computer store has taken off and you now need a database to manage your employees and products. Your business is just the right size to use Microsoft Access for this purpose.
The Assignment You are to create a small business database in Microsoft Access which will be used to keep track of employee and product information for your computer store. For this project, you will build:
Two Tables (One for employee information and one for product information)
Two Forms (Used to view/modify these two tables)
Four Queries (Described below)
One Report (Described below)
The following steps must be taken in order. If you cannot figure out a step, do NOT continue until you have obtained lab tutorial help. Start EARLY and obtain help EARLY!
Step 1 – Create the tables
You first need to create the tables to store all the data – the following fields should be used:
The Employee Table
Employee Number
First Name
Last Name
Street
City
Province
Postal Code
Phone Number
Yearly Salary
The Product Table
Product Number
Product Name
Number in Stock
Retail Price
Discontinued (yes/no)
In the “Design View”, remember to set the Data Type (AutoNumber, Number, Text, Yes/No, etc.) correctly, make a primary key and be sure to include a short description about each field!
Step 2 – Create the forms
For each table, you need to create a form to permit input of data. The “Create form by using Wizard” is by far the easiest method – be sure to include all fields in your form and use a “Columnar” layout. After saving the form, go to the design view and format the forms to make them look nice (eg. pictures, text formatting, headers, footers, etc).
Step 3 – Populate the tables
Open the forms in “Form View” and enter reasonable values (hypothetical) for 10 employees and 15 products. Be sure to use varying data, since the reports and queries will need this in the next steps of the project.
Step 4 – Create the Queries
Create four queries using the “Create query in Design View” option.
o The first query should be based on the employee table and should show the phone numbers of all employees who live in the province of Ontario.
o The second query should be based on the employee table and should show the employee number and full name (first and last name concatenated) of all employees who have a salary greater than $40,000.
o The third query should be based on the product table and should give a list of the product numbers and product descriptions of all products which are not discontinued.
o The fourth query should be based on the product table and should give a list of product names that have more than 20 items in stock OR have a retail price of less than $10.00.
When saved, these queries can either be given descriptive names as to what they do or simply named Query1 through Query4 respectively.
Step 5 – Create the Report
Create a report based on the third query above, showing the product number and product description of the non-discontinued items. For full marks be sure to format the report similar to the way you did the forms (i.e. include a header, footer, etc).
