Problem Solving & Analytic Assignment (Microsoft ACCESS) – creation of relational database and reports
watch CIS 310 Access Assignment Instructions updated finish all about Access database template
CIS 310 ACCESS Assignment
Objective of this assignment is to provide the students the opportunity to develop analytic and problem solving skills, and learn relational database design using Microsoft Access.
For this assignment, you (the student) use Microsoft ACCESS to create a relational database and produce SQL queries and two reports that show total sales and total cost of products.
Tutorial on Microsoft Access is available on Lynda.com. Below are a few suggestions:
? Learn Access 2016: The Basics with Adam Wilbert
? Access 2016 Essential Training with Adam Wilbert
? Access 2016 Advanced Tips and Tricks with Adam Wilbert
Be sure to watch these videos to refresh your knowledge on how to use ACCESS prior to starting the assignment. The prerequisite of CIS 310 is CIS 101 where you learned the basic on how to use Microsoft tools.
Grade for this assignment
Weights Points
Problem Solving & Analytic Assignment (Microsoft ACCESS) – creation of relational database and reports 20% 20
Break-down as follows:
Tables: Customers, Products, Sales Orders, Staff 20% 4
Forms: Customers, Products, Sales Orders, Staff 20% 4
Queries: Total Sales, Product Costs 30% 6
Reports: Total Sales Report, Product Cost Report 30% 6
Instructions:
Below are the tables, forms, queries and reports that you are expected to complete for this assignment (see screen shots)
Here are the steps for you to follow:
1. Create the four tables using specific data (see below) by creating the “tables” in ACCESS and key in these data as shown below (screen shots)
2. Create the four forms from the four tables in ACCESS
3. Create two queries, you may want to use the wizard to do this
4. Create two reports from the two queries
Your Database must have all these tables, forms, queries, and reports. Please follow the naming convention as shown below. A template ACCESS database is given to you with customers, staffs, and products tables already created and populated. Your Job is to create:
1. Sales Order table
2. 4 forms: Customer Details, Product Details, Sales Order Details, Staff Details. An additional Sales Order Details form if you want Extra Credit.
3. 2 queries
4. 2 reports
When you are done your database should look like this ‘screen shot’.
Tables
Use “create’ function and ‘Design View” option to create the tables. You must ensure that appropriate data fields in the tables are of the right data types and “masked” using ‘input mask” option in Access to ensure data integrity.
Customer table – phone number and zipcode are two example in this tables
Design View of Customers table. Note that Zip Code field’s “input mask” to ensure data entry integrity.
Note: Phone Number field’s “input mask” to ensure data entry integrity.
Products Table
StaffTable – SS#, hired date, terminated date, phone number and zip code are “masked” using “mask input” to the right formats. See screen shots below.
Sales Orders
You are expected to create this tables using information from the above three tables. Make sure that Product ID, Employee ID, Customer ID are of data type number. Date of Sales is masked using “Mask input” with the right date format.
Design View of Sales Orders Table
Forms
You are expected to create four forms look like these. Remember the forms are generated from the tables in Access, you use the “create” function then select “form design” to create forms in Access.
Be sure to reformat the forms to look like these using “Design View” and move/drag/drop the fields.
Create Product Details form – Adjust the lengths of the fields appropriately.
Create the Staff form and adjust and re-arrange the fields to the appropriate layout as below.
Create Sales Order Details form. Below is the “layout view” of the sales Order Details form.
Below is the “design view” of the sales Order Details form.
To get the drop down arrow on the product ID field, Employee ID field, and Customer ID filed, click/select the field itself, right click on mouse, a menu shows up, click “change to” then select “combo box”. Notice the column count is 2, and column width is .25”, .25” on the property sheet, this allows the “drop down menu” to show product ID and Product Name for selection purpose.
Set “Limit to List” on the data tab on the Property Sheet to “yes” , this limits the data entry to the pick list for Product ID.
Repeat the same steps above for Customer ID and Employee ID; the column count is 3, and column width is .25”, 1”, 1” on the property sheet, the drop down arrow on the Customer and Employee ID fields show ID, first and last names. Set “Limit to List” on the data tab on the Property Sheet to “yes”.
Queries look like these. Here are the steps:
1. Create them using “Query Wizard” or “Query Design” in the Create function.
2. Establish “relationship” between the tables by connecting the “primary key” fields between each table by drag & drop the identical field from one table to the next:
– “Product ID” in the “products” table to the “product ID” in the “Sales Orders “table
– “Customer ID” in “Customers” table to “Customer ID” in “ Sales Orders” table
– “employee ID” in “Staff” table to “employee ID” in “Sales Orders” table
This is how you establish “rational relationship” between “key’ data fields/identifier.
3. Double click on each fields that are required to build the report, andadd “total sales: [quantity]*[price/unit]” to the last column, be sure to have ‘? check mark’ on all columns. (see screen shots below)
4. Repeat the same steps to create “product costs” query, again add “total cost: [quantity]*[cost/unit]”
Reports
You are expected to create two reports. Create the reports from the queries using report wizard, makes you format the reports as shown below. Make sure you select sort by product ID in ascending order when creating this report using “report wizard”
Product Cost Report
Make sure you select sort by Employee ID in ascending order when creating this report using “report wizard”
Total Sales Report
You use the “design view” in Access to format and line up the columns in the reports (as shown below) so that the reports look line up and easy to read.
Extra Credit Item:
Add text items to the Sales Order Details form so the user can determine the Product, Employee’s First and Last Name, and Customer’s First and Last Name.
Instructions:
Open the Sales Order Details form in the Design view.
Open the Property Sheet for the Sales Order Details Form. Select “Form” on the Property Sheet menu, In the Record Source, click on the “…” select item to create a query. You are creating a query for the form, this is not a query for the report.
When the query opens, click on “select table” to add the Products table, the Staff table, and Customers table. If the tables were set up correctly, the key fields will automatically connect.
Add all the fields from the Sales Order table, the Product Name and Product Description from the Products table, the Employee First and Last Name from the Staff table, and the Customer’s First and Last Name from the Customer’s table by highlighting the fields and dropping them to the grid below, or by double clicking each field. It looks like this.
Close the query, returning you to the form. Be sure you are in the “Form design tool” view, click on “ab|” to add a field to the right of the Product ID field, click on the drop down arrow on ‘control source’ listed on the property sheet, select Product Name field. Repeat the same steps to add Product Description, Employee first and last names to the right of the Employee ID field, and Customer first and last names to the right of Customer ID. Make sure the fields lined up properly