CIS 3100 ACCESS Assignment Online Session
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.
These tutorials on LinkedIn Lynda.com on MyCPP account are helpful for you to complete Access assignment
Learn Access
2
01
6
: The Basics with Adam Wilbert
Access 2016 Essential Training with Adam Wilbert
Access 2016: Queries
Access 2016: Forms and Reports
Be sure to watch these videos to refresh your knowledge on how to use ACCESS prior to starting the assignment. The prerequisite of CIS 3100 is CIS 1010 where you learned the basic on how to use Microsoft tools.
___________________________________________________________________________
Prerequisites: Microcomputer proficiency or CIS 1010, essentially students are expected to be proficient in Microsoft office tools and Access.
Expectations: CIS 1010 is the pre-requisite course where students learned and mastered fundamental Excel and Access skills (level 1). Students who recently transferred from Junior Colleges are assumed to have learned how to use Microsoft tools, Excel and Access during their years in Junior Colleges. In CIS 3100, Students to complete two assignments; 1) advanced Microsoft Excel assignment, and 2) advanced Microsoft Access assignment. Students are expected to be proficient in using Microsoft Excel and Access. These Excel and Access assignments in CIS 3100 are for the students to build level 2 advanced Excel and Access skills.
____________________________________________________________________________
To complete this Access assignment, you must know how to perform the following tasks in sequence from 1 to 6:
1) Build Access Table
2) Perform data entry on Access Table
3) Build Access Forms
4) Define Access Table Relationship via primary keys (i.e. building the relational database)
5) Build Access Queries
6) Build Access Reports
______________________________________________________________________________
Grade for this assignment
Weights
Points
Problem Solving & Analytic Assignment (Microsoft ACCESS) – creation of relational database and reports
30
%
Break-down as follows:
Tables: Customers, Products, Sales Orders, Staff
20%
Forms: Customers, Products, Sales Orders, Staff
20%
6
Queries: Total Sales, Product Costs
30%
9
Reports: Total Sales Report, Product Cost Report
30%
9
Extra Credit: 2 points
Instructions:
Copy the “Access database template.accdb” and name it your own access database as “
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 building your database should look like this ‘screen shot’.
Building Access 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
Staff Table – 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 you created previously in Access, you use the “create” function then select “form design” to create the corresponding forms in Access.
Be sure to reformat the forms to look like these using “Design View” and move/drag/drop the fields.
Use Form Wizard to create your forms.
Select Column
Save and call it “Customer Details”
Click View and Design View then Move/drag/drop the fields to look this the following
Follow similar steps to complete Product Details form, Sales Order Details form, and Staff Details form.
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. First create the “Total Sales” Query by clicking anyone of the four Tables (example: Customers Tables) that were created, click create, click Query Wizard, click ok
3. Select all fields from the Customer Table.
4. Change “Tables/Queries” to Product, select all fields from the Product Table
5. Repeat steps 3 and 4 to select all data fields from Sales Order Table and Staff Table
Select all 4 tables then click add
6. Build Table Relationships – 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.
See screen shots below.
7. Double click on each field that are required to build the report and add “total sales: [quantity]*[price/unit]” to the last column, be sure to have ‘ check mark’ on all columns. (see screen shots below)
8. Repeat the same steps to create “product costs” query, again add “total cost: [quantity]*[cost/unit]”
Make sure all the data fields are listed correctly here, and all data fields in the next screen shot below.
You can scroll and see these data fields.
Repeat similar steps to create Product Costs query
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”
1. Product Cost Report
Make sure you select sort by Employee ID in ascending order when creating this report using “report wizard”
2. Total Sales Report
Click on Product Costs query, select report wizard and name the report as “product costs 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.
Follow the similar steps to create Total Sales Report and line up the columns on the report.
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
2
Customer ID | Customer First Name | Customer Last Name | Customer Address | City | Zipcode | Phone number | |||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
1 | Bill | Smith | 1 | 2 | 3 | 4 | Pomona | 926 | 5 | (909) 555-1212 | bsmith@gmail.com | ||||||
Oscar | Lorenzo | 3567 | Los Angeles | 90006 | (310) 512-6785 | Slorenzo@Aol.com | |||||||||||
Betty | White | 3590 Brand Blvd | Glendale | 91205 | (818) 567-9090 | Bwhite@yahoo.com | |||||||||||
Linda | Sam | 1256 | San Gabriel | 91776 | (626) 526-6787 | Lsam@gmail.com | |||||||||||
Daniel | Awesome | 1236 second st | Alhambra | 91801 | (626) 458-1289 | Dawesome@outlook.com |
Product ID | Product Name | Product Description | Cost/Unit | Price/Unit | Inventory (units) | |
---|---|---|---|---|---|---|
Product A | Sport Watch Unisex | ¤ 3.60 | ¤ 24.00 | 500 | ||
Product B | Sport Watch Boy | ¤ 3.00 | ¤ 20.00 | 300 | ||
Product C | Sport Watch Girl | ¤ 2.40 | ¤ 16.00 |
Sales ID | Employee ID | Quantity | Date of Sale |
---|
Employee First Name | Employee Last Name | Employee SS# | Date Hired | Date Terminated | Salary Per Hour | Home Address | Home or Cell Phone # | |||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Mary | Burns | 555-23-1234 | 9/1/16 | 1/1/99 | ¤ 10.00 | 910 E. Amar St | Walnut | 91789- | (909) 698-1234 | |||||
Rich | Raminez | 565-32-1111 | 1/1/91 | 4500 Asuza Ave #120 | West Covina | 91790- | (626) 928-4787 | |||||||
Julia | Shen | 251-11-4567 | 7690 Nogales St #45 | Rowland Heights | 91748- | (626) 912-4569 |