BCIS 1305 North Lake College Microsoft Access Database

Objective:

Organize, manage and analyze information using database software

Requirements:

For this assignment, you will be creating a Microsoft Access database for the BCIS 1305 PC Repair Company. You will:

• Create the database

Create a table

Add data to the table

Create tables using an Excel spreadsheet

Create a form

Create reports

Select data for decision support

• Requirements:
• Create a new database in MS Access called BCIS1305
• Create a table called ChargeRates with the following fields
• Position – Primary Key – character data – 30 characters max
• ChargeRate – decimal in format 00.00
• Add data to the table for each position:PositionChargeRateTech 125.00Tech 235.00Sr. Tech50.00
• Use the attached spreadsheet to add 5 more tables to the database: Customers, Employees, Jobs, Parts, JobDetails.

Add 2 columns to the Jobs table: AmountDue (decimal: 000.00) and Paid (binary)

Create relationships between the 6 tables as follows:
Jobs to Customers using CustomerID
Jobs to Employees using EmployeeID
Jobs to JobDetails using JobNo
JobDetails to Parts using PartNo
ChargeRates to Employees using Position

Create a form to add Customers to the Customer Table. Use the form to add 2 new customer to the table (data of your choice).

1. Create a form to add Jobs to the Jobs Table. Add 2 new jobs to the table.
2. Create queries

Display all fields for all Customers in the Customers table

• Display the Jobno, DateRequested, DateCompleted and HoursBilled for all Jobs in the Jobs table
• Display the JobNo, DateRequested, DateCompleted and HoursBIlled for all Jobs for EmployeeID = 3

1. Display the JobNo, DateRequested, DateCompleted and HoursBIlled for all Jobs with HoursBilled > 1

Display the JobNo, DateRequested, DateCompleted and HoursBIlled from the Jobs table, plus the LastName, FirstName and Position from the Employees table for all Jobs with HoursBilled > 1

Display the JobNo, DateRequested, DateCompleted and HoursBilled from the Jobs table, plus the LastName, FirstName and Position from the Employees table for all Jobs with HoursBilled > 1

Display the JobNo, DateRequested, DateCompleted and HoursBilled from the Jobs table, plus the LastName, FirstName and Position from the Employees table plus the CustomerName from the Customers table for all Jobs with HoursBilled > 1

Create reports

Create a report of all Customers in the Customers table

Create a report using query g above for all jobs in May and June of 2021 that includes the JobNo, Date Requested, DateCompleted, HoursBilled, Employee LastName, Employee FirstName and CustomerName

Create a report that lists the CustomerName, Address, City, and PostalCode fromthe Customers table, the JobNo, DateCompleted, HoursBilled and AmountDue from the Jobs table for all jobs that have not been paid.

Create a report with the JobNo, DateRequested, DateCompleted, Employee Lastname, Employee FirstName, HoursBilled and AmountDue for all jobs where the DtaeRequested is different from the DateCompleted.