Question Details

[answered] MIS301/303 Assignment #6 Access Be Careful: It accounts for

Business information system questions.

This work is access assignment with 7 question to do. Could any tutor help me out?

I attached 2 files.?

MIS301/303 Assignment #6 Access


Be Careful:


(1) This is an individual assignment. It accounts for 10% of your grade.


(2) Due Date: November 17th, 2016 Thursday 10:30:59am


(3) Your final solution should be ONE Microsoft Access 2013 file (.accdb file) that is


submitted via Blackboard. Before you submit your solution to Blackboard, close the


Microsoft Access 2013 application on your computer. Then use Blackboard to select


your solution file. Otherwise, you may submit the wrong temporary .laccdb file that I


will not be able to open and you will get a 0 grade.


(4) Pay attention to details: all questions are detail-oriented. Make sure you read the


instruction word by word carefully. Otherwise, you might lose points for failing to fulfill


any of the clearly specified requirements.


(5) You may want to submit your solution at least 30 minutes before the deadline. Make


sure you click ?Submit? rather than ?Save Draft? to actually submit. You are required to


double-check your submission by downloading it and opening it to see if it is your final


version. You will be responsible for any technical difficulty or mistake on your side that


results in submitting the wrong work or your work not being submitted or being accepted


as late. No exceptions will be made.


(6) Make good use of the tutorial for the in-class exercise. If you forget about some of the


skills we have covered in class, you can always refer to the tutorial posted on Blackboard


which documents every step we have done for the in-class exercise.


Assignment #6: Access ? Create Queries/Reports for AHI -- Continued (Total: 50 pts)


A1 Home Improvement (AHI) is a home improvement retailer in the Northern Virginia area.


AHI does not employ installers, but rather subcontracts the installations out to a list of


approved installers. Whenever a customer purchases an item in the store, an AHI agent will


help create a contract for the installation. Then, AHI will try to assign an installer for the


installation contract. After the assignment, the installer will go to the customer?s house and


complete installing the item. Currently, this process is primarily paper-based and does not


provide staff with the information they need to efficiently complete contracts. Frequently, the


merchandising group arbitrarily runs advertisements for specials such as a guaranteed 2 week


installation of ceiling fans. Without a way to effectively manage the contracts and installers,


contracts don?t get completed in a timely manner which has resulted in customer




You are hired by AHI?s IT department and you have been asked to create a tracking system to


automatically track these installations. In the first task, you have created the E-R diagram for


the database. In the second task, you have created the database and entered the data into the




Now your third task is to create queries and reports for the manager of AHI to make business


decisions. If you are unsure about the meaning of an attribute, you should go the design view


of the table and read the description of the attribute.


Page 1 of 3 MIS301/303 Assignment #6 Access


Q0. Download the database (Assignment #6_Access.accdb) from Blackboard. Rename it


exactly as your last name followed by your G number without ?G? and then followed by


suffix ?_assignment_6?. There should not be extra spaces or underlines in between. For


example, the file?s full name should be like Ye12345678_assignment_6.accdb, in which


.accdb is the file extension. You will work on Q1-Q7 based on this access database and


then submit it via Blackboard. (1 pts)


Q1. Create a query named ?Installer Charge Query? using data from the Installer table to list


installers who charge more than $170 per day. Include the installer?s ID, first name, last


name, phone number, and daily charge. Sort the results by daily charge in the descending


order. Hint: use criteria. (5 pts)


Q2. Create a query named ?Unassigned Contract Query? using data from the Contract table


and the Customer table to list the contracts that haven?t been assigned an installer yet.


You will need to know the contract ID, the date the contract was created and a


description of the work to be performed. You will also need to know the customer?s first


name, last name, city and phone number since installers are assigned based on their


location relative to the customer?s location. Hint: use criteria for the correct field and do


not show it in the query results. (5 pts)


Q3. Create a query named ?Overtime Contract Query? using data from the Contract table and


the Installer table to list the contracts that took longer than 2 weeks (14 days) to be


completed. How long it took for an installer to complete an installation should be 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). This will require adding a calculated field using the DateDiff function to


the query (remember that in the formula each attribute of a table should be enclosed by a


square bracket). Include the contract ID, the description of the work for the installation,


installer?s first name, last name, phone number, and days to complete the contract in the


query. Sort the results by days to complete in the descending order. Hint: remember to


use criteria for the calculated field so that only contracts taking more than 14 days to


complete are shown. (5 pts)


Q4. Create a query named ?Contract Profit Query? using data from the Contract table and the


Installer table to determine the profit for AHI generated from each installation. Include


contract ID, ContractItem, InstallerID, and ContractServiceCharge. Then add a field


?InstallationTime? 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, which equals the installer?s daily charge * InstallationTime. Finally, add a field


?Profit? to compute the profit from the contract, which equals ContractServiceCharge


minus InstallerCost. Sort the results by ContractID in the ascending order. (10 pts)


Q5. Create a query named ?Customer Revenue Query? 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, total


number of services, and total revenue. For a given customer, total revenue is 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


Page 2 of 3 MIS301/303 Assignment #6 Access


easy to understand. Hint: choose the appropriate total functions; you do not need to use


criteria. (10 pts)


Q6. Create a query named ?Installer Performance Query? 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 installer?s average rating by customers. Include the installer ID,


installer?s first name, last name, number of assigned installations, and average rating in


the query. Sort the results by average rating in the descending order. Make sure that all


the column labels in the query results are informative and easy to understand. Hint:


choose the appropriate total functions. For example, you should choose ?Avg? in the total


function field to calculate average. (10 pts)


Q7. Create a report to display the results of query Q3 above. Include an appropriate title for


the report. Include in the report installer?s first name, installer?s last name, contract ID,


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 installer?s first name and last name and then sort the results by how long it


took to complete the installation in the descending order (i.e. the calculated days to


complete from the query). 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: (4 pts) Page 3 of 3


Solution details:

This question was answered on: Sep 18, 2020

PRICE: $15 (25.37 KB)

Buy this answer for only: $15

This attachment is locked

We have a ready expert answer for this paper which you can use for in-depth understanding, research editing or paraphrasing. You can buy it or order for a fresh, original and plagiarism-free copy from our tutoring website (Deadline assured. Flexible pricing. TurnItIn Report provided)

Pay using PayPal (No PayPal account Required) or your credit card . All your purchases are securely protected by .

About this Question






Sep 18, 2020





We have top-notch tutors who can do your essay/homework for you at a reasonable cost and then you can simply use that essay as a template to build your own arguments.

You can also use these solutions:

  • As a reference for in-depth understanding of the subject.
  • As a source of ideas / reasoning for your own research (if properly referenced)
  • For editing and paraphrasing (check your institution's definition of plagiarism and recommended paraphrase).
This we believe is a better way of understanding a problem and makes use of the efficiency of time of the student.


Order New Solution. Quick Turnaround

Click on the button below in order to Order for a New, Original and High-Quality Essay Solutions. New orders are original solutions and precise to your writing instruction requirements. Place a New Order using the button below.


Order Now