INFO123-22S2 MS Access Assignment
Microsoft Access
(Course Weighting 7%)
Due: Friday 14th October at 9 p.m.
Introduction:
For this assignment, you are required to modify an Access database file named
‘Service_Call_DB.accdb’ which is available for download from the ‘Access Assignment’ page on
the INFO123 LEARN site.
You are required to use Microsoft Access to complete all the tasks given below.
IMPORTANT:
Make sure to save your file on a regular basis. When you have completed the assignment, you will
need to upload the completed file to LEARN using the option provided.
When you open Access, it produces a small ‘swap’ file. Please close Access before uploading the
completed file, as the swap file is frequently uploaded instead of the database itself.
Before you begin the task below, complete the following steps:
1. Download the ‘Access File’ ZIP folder and extract ‘Service_Call_DB.accdb’ file to your
Computer. This is the Access file you will work on, and then upload the final file to
LEARN when you are finished.
2. Rename the Service_Call_DB.accdb to include your username,
as Service_Call_DB_UserCode.accdb (e.g., Service_Call_DB_abb140.accdb).
Scenario
You have been contracted to develop a basic information system for a local appliance servicing and
repair company, and your first task is to add the features listed below to the company’s current
database, ‘Service_Call_DB’.
Required:
Part A - Table (8 marks)
1. Create a new Table using the structure shown below:
a. Name the table: ‘Customers’.
2
b. Set the ‘CustomerID’ field as the Primary Key.
c. Set the ‘ContactFirstName’ and ‘PhoneNumber’ fields to Required so that they must
always have a value.
2. Add the following data into the ‘Customers’ table:
3. Modify the current relationships in the database to include the newly created ‘Customers’
table.
Part B - Form (9 marks)
4. Create a new Form based on the ‘Employees’ table with the following specifications
(Recommend using the Form Wizard):
a. Use All the available fields from the ‘Employees’ table EXCEPT ‘OfficeLocation’ and
‘DepartmentName’.
b. Layout: Columnar.
c. Form Title: ‘Employee Information Form’.
5. Replace the ‘Title’ Text Box with a Combo Box.
a. Fill in the following values for the drop-down list that the user will be able to manually pick
later.
− Manager
− Customer Service Rep
− Technician
− Helper
b. Store the values in the field ‘Title’.
c. Rename the Combo Box field as ‘Title’.
d. Resize the Combo Box so that it is in-line (roughly) with the other Text Boxes.
6. Modify the Tab Index of the controls so that the ‘Title’ Combo Box comes after the
‘LastName’ Text Box when you use the Tab key to go through the elements.
7. Save the form as ‘Employee_Details’.
3
Part C - Query (9 marks)
8. Create a Query using the specifications below:
a. Include fields:
− WorkOrderNo, DateReceived, DateRequired, MakeAndModel, ProblemDescription,
and PickedUp (from Workorders Table)
− PartName (from Parts Table)
− UnitPrice and Quantity (from Workorder_Parts Table)
− FirstName and LastName (from Employees Table)
b. Add a criterion to ‘PickedUp’ that ONLY returns work orders that have NOT been picked
up.
c. Sort ‘PartName’ field in Descending order.
d. Create a new field called ‘EmployeeName’, which is made up of ‘FirstName’ and
‘LastName’. Include a comma (,) between the ‘FirstName’ and ‘LastName’.
e. Create a calculated field called ‘TotalPartCosts’ with a formula for determining the total
part pricing i.e., multiply ‘UnitPrice’ by ‘Quantity’ (You may want to mention the related
table name when specifying fields in the expression). Format the results with a dollar sign
and two decimal places.
f. Modify the query to display only ‘EmployeeName’, ‘WorkOrderNo’,
‘MakeAndModel’, ‘ProblemDescription’, ‘PartName’, and ‘TotalPartCosts’. If you run
the query, it should look like this.
9. Run and save the query as ‘Employee_PartCosts’.
Part D - Report (9 marks)
10. Create a Report based on the ‘Employee_Workloads’ query with the following specifications
(Recommend using the Report Wizard):
a. Use ALL the available fields from the ‘Employee_Workloads’ query EXCEPT
‘MakeAndModel’and ‘ProblemDescription’.
b. Group by: ‘EmployeeName’.
c. Layout: Stepped.
d. Orientation: Landscape.
e. Report Title: ‘Employee Tasks Report’.
f. Bold and Underline the report title.
g. Adjust column widths in Design View as necessary.
4
11. Include a Group Footer that displays the ‘Total number of orders worked’ and ‘Total
number of hours worked’ for EACH ‘Employee’ (You may want to use the Group & Sort
Tool).
12. Modify the field text labels appropriately to match Figure 1 below.
13. Save the report as ‘Employee_Workloads’.
Once you are finished, save your ‘Service_Call_DB_UserCode.accdb’ file and upload it to the
‘Access Assignment’ drop-box in LEARN.