[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

 

dissatisfaction.

 

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

 

database.

 

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:

About this Question

STATUS

Answered

QUALITY

Approved

DATE ANSWERED

Sep 18, 2020

EXPERT

Tutor

ANSWER RATING

GET INSTANT HELP/h4>

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.

NEW ASSIGNMENT HELP?

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.

WE GUARANTEE, THAT YOUR PAPER WILL BE WRITTEN FROM SCRATCH AND WITHIN YOUR SET DEADLINE.