THIS IS AN ACCESS ASSIGNMENT

A1 Home Improvement (AHI) provides installation services for home improvement in Northern Virginia. AHI does not employ installers, but rather subcontracts the installations out to a list of approved installers. Whenever a customer requests an installation service in the store, a contract is created for the installation. AHI offers different payment options such as 20% deposit+80% after completion, 30% deposit+70% after completion, etc. AHI decides a deposit amount based on the installation job and the customer pays upfront. Then AHI will assign an installer for the installation job in the contract. Afterwards, the installer will go to the customers house and complete the installation. Currently, this process is primarily paperbased and does not provide management with the information needed to efficiently monitor contracts and installers. AHI often runs advertisements for specials such as guaranteed 2-week installation for ceiling fans. Without a way to effectively manage installations, installation service sometimes does not get completed within the promised timeframe which has resulted in customer dissatisfaction. You are hired by AHI to create a database that helps automatically tracks installation contracts and installers. In the first task, you have created the E-R diagram for the database. In the second task, you have created the database using Microsoft Access 2016 and entered data into the database. Now your third task is to create queries and reports for AHI management to make business decisions.

A1 Home Improvement (AHI) provides installation services for home improvement in Northern Virginia. AHI does not employ installers, but rather subcontracts the installations out to a list of approved installers. Whenever a customer requests an installation service in the store, a contract is created for the installation. AHI offers different payment options such as 20% deposit+80% after completion, 30% deposit+70% after completion, etc. AHI decides a deposit amount based on the installation job and the customer pays upfront. Then AHI will assign an installer for the installation job in the contract. Afterwards, the installer will go to the customers house and complete the installation. Currently, this process is primarily paperbased and does not provide management with the information needed to efficiently monitor contracts and installers. AHI often runs advertisements for specials such as guaranteed 2-week installation for ceiling fans. Without a way to effectively manage installations, installation service sometimes does not get completed within the promised timeframe which has resulted in customer dissatisfaction. You are hired by AHI to create a database that helps automatically tracks installation contracts and installers. In the first task, you have created the E-R diagram for the database. In the second task, you have created the database using Microsoft Access 2016 and entered data into the database. Now your third task is to create queries and reports for AHI management to make business decisions.

Q1.To save cost, the management is considering getting rid of overcharging installers. Create a query named Overcharging Installers using data from the Installer table to list installers who charge more than $200 per day. Include the installers ID, first name, last name, phone number, and daily charge. Sort the results by daily charge in the descending order. (5 pts)

Q2. The management wants to ensure that an installer is assigned to a newly created contract as early as possible. Create a query named Installer Unassigned Contract using data from the Contract table and the Customer table to list the contracts that havent been assigned an installer yet so that the management can handle them immediately. The management needs to know the contracts ID, a description of the installation to be performed, and the date the contract was created. The management also wants to know the customers city and phone number since installers are assigned based on their location relative to the customers location. (5 pts)

Q3. AHI often send advertisements such as guaranteed 2-week installation. The management wants to examine if each installation is finished within the promised timeframe. Create a query named Overtime Contract using data from the Contract table and the Installer table to list the installations that took longer than 2 weeks (i.e., 14 days) to complete. How long it took for an installer to complete an installation is measured as the interval in days between ContractDateAssigned (the day an installer was assigned and started to work on the installation) and ContractDateCompleted (the day the installation was finished). Include the contract ID, the description of the work for the installation, the installers first name, last name, phone number, and number of days used to complete the contract in the query. (5 pts)

Q4. The management is interested in knowing the profit generated by each installation. Create a query named Contract Profit using data from the Contract table and the Installer table to determine the profit for AHI generated from each installation. Include contract ID, ContractDescription, ContractServiceCharge, InstallerID, and InstallerDailyCharge. Then add a field InstallationDays to compute the number of days used to complete the installation as you did for Q3. Add a field InstallerCost to computer how much AHI paid to the installer. Finally, add a field Profit to compute the profit from the contract, which equals ContractServiceCharge minus InstallerCost. Sort the results by ContractServiceCharge in the descending order. (10 pts)

Q5. The management wants to identify loyal customers to send them a thank you letter. Create a query named Customer Summary using data from the Contract table and the Customer table to list the count of requested services and total revenue generated by each customer. Include Customer ID, Customer first name, customer last name, customer phone. Then add a field shown as Number of Services to display the total number of requested services by the customer and a field shown as Total Revenue to display the sum of ContractServiceCharge for all installations the customer has requested. Sort the results by Total Revenue in the descending order so that the manager knows who the most valuable customer is. Make sure that all the column labels in the query results are informative and easy to understand. (10 pts) MIS303 Microsoft Access Assignment #2 Page 3 of 3

Q6. The management wants to evaluate each installers performance based on the customers rating of their work. Create a query named Installer Performance using data from the Contract table and the Installer table to list the number of installations each installer has been assigned to work on and the installers average rating by customers. Include the installer ID, installers first name, and last name in the query first. Then add a field shown as Number of Installations to display number of installations the installer has been assigned to. Then add a field shown as Average Rating to display the average rating of the installer by customers in the query. Sort the results by average rating in the descending order. Hint: you should use Avg in the total function field to calculate average. (10 pts)

Q7. Create a report to display the results of query Overtime Contract in Q3 for the management. Include an appropriate title for the report. Include in the report installers first name, installers last name, contract ID, contract description, and days taken to complete the contract. Remember that this report will be used to highlight installers who have not completed the installations in a timely fashion. Group this report by installers first name and last name and then sort the results by how long it took to complete the installation in the descending order. Make sure that each title/field is sufficiently wide that the data will not be shown as #### and the label for each column will be fully shown. Hint: your report should look like below