In this exercise, you will use database software to develop an application that

Question

In this exercise, you will use database software to develop an application that tracks customer service requests and analyzes customer data to identify customers meriting priority treatment.

Prime Service is a large service company that provides maintenance and repair services for close to 1,200 commercial businesses in New York, New Jersey, and Connecticut. Its customers include businesses of all sizes. Customers with service needs call into its customer service department with requests for repairing heating ducts, broken windows, leaky roofs, broken water pipes, and other problems. The company assigns each request a number and writes down the service request number, identification number of the customer account, the date of the request, the type of equipment requiring repair, and a brief description of the problem. The service requests are handled on a firstcomefirstserved basis. After the service work has been completed, Prime calculates the cost of the work, enters the price on the service request form, and bills the client. Management is not happy with this arrangement because the most important and profitable clients those with accounts of more than $70,000 are treated no differently from its clients with small accounts. It would like to find a way to provide its best customers with better service. Management would also like to know which types of service problems occur most frequently so that it can make sure it has adequate resources to address them.

Prime service has a small database with client account information.

Use your MS Access to design a solution that would enable Prime’s customer service representatives to identify the most important customers so that they could receive priority service. Your solution will require more than one table. Populate your database with at least 10 service requests. Create several reports that would be of interest to management, such as a list of the highest and lowestpriority accounts or a report showing the most frequently occurring service problems. Create a report showing customer service representatives which service calls they should respond to first on a specific date. I need sql codes which you will use in order to complete this task.

Explanation / Answer

Highest priority customer accounts (Top 5 customers):

select top 5 * from tblACCOUNT order by DOLLAR_SIZ desc

Lowest priority customer accounts (Last 5 customers):

select top 5 * from tblACCOUNT order by DOLLAR_SIZ asc

Most frequently occurring service problems(Top 3 problems):

SELECT TOP 3 *
FROM (SELECT EQUIP_TYPE As Issue, count(EQUIP_TYPE) As NoOfOccurence FROM tblREQUEST GROUP BY EQUIP_TYPE) a
ORDER BY NoOfOccurence Desc

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.