Access Exam Fall $2024$: Landscaping

Description Properly name your tables Customers and Landscapers

$1).$ Customers table.

$2).$ Landscapers table.

$2.$ Database Application Requirement: Using Access $($Total $100$ points$)$

Please use your last name as your Access file name, save the file, and then submit the file to the Canvas. Properly SAVE the file before vou begin the following steps. Choose the proper field data types. Do not add any new fields other than what is provided above in your tables.

a$.$ Create two tables for the data, set a primary key for each table. Input the data properly. $(10$ points$)$

b$.$ Create relationship and specify referential integrity between the two tables. $(10$ points$)$

c$.$ Create a query named QueryC to display customers $($CustomerName$,$ AccountBalance, and EmployeeName$)$ that have an account balance less than or equal to $\backslash (\backslash$$ $100\mathrm{.}00\backslash ).(5$ points$)$

d$.$ Create a query named QueryD to find the total Account Balance of all customers due $($note: display only total of all outstanding due $-$ONE VALUE$).(5$ points$)$

e$.$ Create a query named QueryE to list CustomerName, AccountBalance, EmployeeName for the customers who have an Account Balance greater than $\backslash (\backslash$$ $200\backslash )$ or $\backslash (\backslash$$ $0\backslash ).(10$ points$)$

f$.$ Create a query named QueryF to add a calculated field that displays a new employee pay rate that is $\backslash (110\backslash \%\backslash )$ of the current EmployeePayRate. Use UpdatedPayRate for the new field name. Group andonly Display EmployeeName and UpdatedPayRate properly formatted showing the four employees and UpdatedPayRate. $(10$ points$)$

g$.$ Create a query named QueryG to find the total account balance due grouped by EmployeeNo $(10$ points$)$

h$.$ Create a parameter query named QueryH and display only the fields CustomerNo, CustomerName, and EmployeeNo. Test it entering Lee for the Employee name. $(10$ points$)$

i$.$ Create a calculated field query named QueryI that computes overtime. Overtime is HoursWorked $-40.$ Display the hours of overtime and EmployeeName if result is over $0.$ Name the field Overtime. Display properly only employees with overtime and the overtime hours. $(10$ points$)$

j$.$ Create a single record form for the customer's table. $(10$ points$)$

k$.$ Create a stepped formatted report to show EmployeeNo and Name and the related Customers' Name and Account Balance Due. Allow default report title. Display portrait view and show entire data labels and textboxes. $(10$ points$)$