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 first-come-first-served 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. The database table includes fields for the account ID, company (account) name, street address, city, state, zip code, account size (in dollars), contact last name, contact first name, and contact telephone number. The contact is the name of person in each company who is responsible for contacting Prime about maintenance and repair work. 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 lowest-priority 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.