A-TECH is an automotive service and repair company that receives a range of customer requests which include: regular vehicle servicing, repairs, performance modifications and Road Worthy Certificate inspections.A-TECH employs mechanics (called Technicians) who are able to carry out a broad range of automotive activities for the company. Depending on the type of a customer request, it can completed by either one or several of the technicians at a time, however each technician can only be assigned to one request at a time.
A customer may make one or more requests (‘Job’) when they call A-TECH, and details about the customer and the type of requests (‘Jobs’) need to be tracked. Requests are identified by a RequestID field. Also A-TECH needs to know the details of the technician who completed a request. Upon completion of a request, the customer receives an invoice that details the work done and the amount that must be paid. A-TECH needs to track all the invoices sent to customers and ensure payment for completed requests is received.
Create an appropriate relationship between the 2 tables and enforce referential integrity.
Create a Form:
1. Create a new form, ‘Columnar style’ based on the ‘Invoice’ table and include all the fields from the Invoice table. Name the form Invoice
2. Modify the look of the form and place a SIMPLE and CLEAR image of a car being repaired (find this image from an online search) in the header section of the form. Add a company name to yourform. Add your full name and student ID in the footer section of the form. Don’t forget to type it in likethis: FirstName LastName YAPAC1702
3. Change the background colour of all sections in the ‘Property Sheet section of the form.
1. Create a calculated field in the Invoice table in a new column with the name ‘Discounted Invoice Amount’ with a data type of currency (in the Field Properties window). Use this field to work out the 10% discount that is removed from the total of the Invoice amount.
Create these queries to answer the questions below:
1. List all the customers who live in area ‘4216’ and include all the fields. Save the query as ‘Customer Area’.
2. The total value of all discounted invoices for each customer, showing the field CustomerID, LName inthe Customer table, and the fields Discounted Invoice Amount in the Invoice table. Save the query as ‘Invoice_Total’. Hint: you will need to use the in the Show/Hide toolbox in the Design Tab. Alongwith
3. List all the invoices (showing all the fields) that have an Amount of $3,999 or less. Savethe query as
Create a Report
1. Create a Tabular report based on the query ‘Invoice_Total’ and sort in ascending order by, and order by
2. Save as‘Job_Total’.
This assignment is to be submitted through the ‘Assignment 3 submission link’ in week 12 ofthe portal. You will be shown this process in Week 11. Students must submit a zipped folder containing the Access Database file and the ERDtemplate document. The file must be zipped using Winzip or 7ZIP (as a .zipfile). Name each file with your Name and ID number, then name the FOLDER asshown.