Individual Access Project

1

CIS 3100 – Assignment 2

Database Design and Implementation Project

Products on Sale Database for Kahdea Inc.

Project Objectives:

This project was designed to give you a better understanding of how data can be organized into

a relational database. A database is used to provide information to solve business problems, to discover

business opportunities, and to manage business units. The project requires the use of Microsoft Access

2016.

Company Background

Kahdea Inc. is a small startup company that sells sports merchandise online. The mission of the

company is to successfully sell and deliver sports products for all. The company sells products for a wide

variety of sports including football, basketball, baseball, soccer, hockey, and volleyball.

Kahdea is composed of 28 employees who work in different departments (production,

operations, automation, accounting, human resources, finance, marketing, etc.). During the probation

period, all employees are trained before being placed into their positions.

Kahdea is planning to have their annual sale in the next month. This sale is an important event

for the company, because it typically generates 31% of Kahdea’s annual revenue. The company has been

planning this sale for a while, and wants to make sure all transactions are made as smoothly as possible.

Task for student:

You are a new hire at Kahdea. During your training, you will learn about relational databases

using Microsoft Access. The database is used to keep track of Kahdea’s sales during this period. Although

the database has been designed, the team needs assistance inserting data, using forms, creating queries

and reports.

Your first training task requires you to watch the Microsoft Access training videos on

Lynda.com. After you become familiar with Microsoft Access, open the Kahdea.accdb file.

Create a Table

Before you begin, the team wants to ensure you understand table structure. They would like

you to create a table for Employees and populate a few records. The table should include the field

names: EmployeeID(PK), Last Name, First Name, and Phone.

2

Step 1: Click the “Create” tab on the top ribbon and select “Table Design”.

Step 2: Enter the Field Names and Data Type. Include a Primary Key (EmployeeID) for the table.

Step 3: Save the table as “Employee”.

Step 4: Enter the following records into the table.

Last Name First Name Phone

Adams Pam (909) 869-5438

Fong-Chen Stella (909) 869-2360

Rios Dario (909) 869-5079

Step 5: After you enter the records, close the table. The entries should be saved.

3

Importing Data

In preparation for the sale, the team has gathered information regarding products’ supplier, and

customers that need to be entered into the system. Since there is a lot of information to upload, using

the forms will not be effective. Microsoft Access allows for a bulk insert of data.

Populate Customer table with data

For this task you will need the Customer.xlsx file.

Step 1: Right click on the Customer table (Under All Access Objects panel) and select “Import”, choose

“Excel”.

Step 2: In the pop-up window click “Browse” and navigate to the Customer.xlsx file. Select the file.

Step 3: Click “Append copy of the records to the table” and in the drop-down menu choose

“Customer”, click “OK”.

Step 4: Click “Next” until the final window. Click “Finish”.

4

Populate ProductSupplier table with Data

For this task you will need the ProductSupplier.txt file.

Step 1: Right click on the ProductSupplier table (under All Access Objects panel) and select “Import”.

Choose “Text File”.

Step 2: In the pop-up window click “Browse” and navigate to the ProductSupplier.txt file, choose the

file.

Step 3: Click “Append copy of the records to the table” and in the drop-down menu choose

“ProductSupplier”, click “OK”.

Step 4: Click “Next” until the final window. Click “Finish”.

5

Create Forms

As a new hire at Kahdea Inc. you are tasked to create forms. Database administrators can enter

the data directly into the tables. However, your boss feels it would be more efficient to create a form

that guides the users to enter the information in the correct order. You need to create a form to enter

suppliers and product category.

Create the Supplier Form

Follow the proceeding steps to use a Form Wizard to create a data entry form with a tabular layout.

Include all fields except Webpage and Notes.

Step 1: Click the “Create” tab on the top ribbon and select “Form Wizard”

Step 2: In the popup window select the Supplier Table from the drop-down menu (Tables/Queries).

Select the fields you wish to use and move them to the Selected Fields box by clicking the “>” button.

Click “Next”.

Step 3: Click and select “Tabular”, and click “Next”.

Step 4: Name the form Populate Supplier, and click “Finish”.

Step 5: Click the New Record icon.

Step 6: Populate the form with new entries using the table given below, once all entries are entered,

save the form. Once finished, close the form.

Field Input

CompanyName Iamz Co

Address1 153 9th Street

City Brea

State CA

Zip 92821

Country USA

Phone 7148884565

FaxNumber 6523937595

Create the ProductCategory Form

Follow the proceeding steps to use a Form Wizard to create a data entry form with a columnar layout, to

add data into the ProductCategory table. Include all fields except ProductCategoryID and Active.

6

Step 1: Click the “Create” tab on the top ribbon and select “Form Wizard”.

Step 2: In the popup window select the ProductCategory table from the drop-down menu

(Tables/Queries). Select the fields you wish to use and move them to the Selected Fields box by clicking

the “>” button. Click “Next”

Step 3: Click and select “Columnar”, and click “Next”.

Step 4: Name the form Populate ProductCategory, and click “Finish”.

Step 5:Click the New Record icon.

Step 6: Populate the form with new entries using the table given below, once all entries are entered

save the form. Once finished close the form.

Field Input

ProductCategory Bodybuilding

7

Creating Relationships

Although the tables have been created, some of them are missing relationships. Without table

relationships, inserts, updates, or deletions in one table, data will not propagate to the other tables. You

will need to create the relationships for all tables in the database. Note: All tables have at least one

relationship while some have two.

Step 1: Click the “Database Tools” tab on the top ribbon and select “Relationships”.

Step 2: If a table is not displaying on the screen, click the “Database Tools” tab on the top ribbon and

select “Show Table”, on the popup window and click “Add”. After selecting any missing Tables, click

“Close”.

Step 3: Drag the Primary Key from the first table to the Foreign Key on the second table. A new window

will appear displaying the joint keys. Check “Enforce Referential Integrity”. Click “Create” to create the

relationship.

Step 4: Continue creating relationships for tables that do not have relationships.

Step 5: Save all changes and the close the workspace.

8

Create Queries

Kahdea Inc. wants to gather information from the database. These queries will be for influencing

decisions involving inventory and logistics.

1. Create a query that displays the Product Code, Product Description, and number of times the

Product was sold. Limit to the results to products that were sold at least 10 times. Save the

query as MostProductsSold.

2. Create a query that displays Customer First Name, Customer Last Name, Order ID, Order Date,

Product Code, Product Description, Quantity Sold, Price, and Total Amount per Product. Limit

the results to Orders between 07/06/2017 and 09/07/2017. Save the query as CustomerOrders.

3. Create a query that displays the Company Name, Products Code, Product Description, Purchase

Price, and Sale Price. Limit the query to products where purchase price is greater than sale

price. Save the query as ProductPrices.

Step 1: Click the “Create” tab on the top ribbon and select “Query Design”.

Step 2: In the popup window, select the table you need for the query and click “Add”. Once all tables are

selected, click the “Close” button.

Step 3: Select the fields required for the query.

Step 4: Enter criteria for the query.

Step 5: Click the “Run” button. Verify the query displays the correct data.

9

Reports

Your boss is impressed with the queries you created and wants to provide the information to

the CEO. You will need to display the information in a presentable format. Create a report to display the

information from the queries.

Create a report to display CustomerOrders. Include Customer First Name, Customer Last Name, Order

ID, Order Date, Product Code, Product Description, Quantity Sold, Price , Total Per Product. In the

header, include the title “CustomerOrders”. In the footer, include the the Run Date/Time, page number

and total pages. Save the report.

Step 1: Click the “Create” tab on the top ribbon and select “Report Wizard” on the far right.

Step 2: In the popup window, select the query from the drop-down menu (Tables/Queries). Select the

fields you wish to use and move them to the Selected Fields box by clicking the “>” button. Click “Next”.

Step 3: Select the fields to group by. This is optional and is not always required. Click “Next”.

Step 4: Select the “sort order” of the report. Click “Next”.

Step 5: Select the Format of the report and the Orientation. Click “Next”.

Step 6: Enter the name for the report. Click “Finish” and the report will display as a print preview.