How to Calculate Defects per Unit (DPU), Defects per Opportunity (DPO), & Defects per Million Opportunities (DPMO) in Access?

MS Acess, SQL. Queries, Solution

Cars.accdb database assignment to create table and make relations. The assignment is related to cars.accdb relation database with queries writing and operation improvement to calculate the following measures:

  • Proportion Defect,
  • Defects per Unit (DPU),
  • Defects per Opportunity (DPO), &
  • Defects per Million Opportunities (DPMO)

Requirements

Instruction: This part of the assignment is to give you an opportunity to exercise relational DB application with hands-on experience. You must use the latest version of Access (e.g., Access 2016 for PC compatible). Download Cars.accdb from the Moodle.

First, import all tables (Car, Purchase, SalesRepresentative & Customer) from Cars.accdb and specify properties for all attributes instructed below

Car table

VehicleID – Text (Data type) – 5 (Field size) – Vehicle ID (Caption) Primary Key (Description) Manufacturer – Text (Data type) – 30 (Field size)

Model – Text (Data type) – 30 (Field size) Class – Text (Data type) – 2 (Field size)

Transmission – Text (Data type) – 2 (Field size) – Trans (Caption) Year – Text (Data type) – 4 (Field size)

Cost – Currency (Data type) – Currency (Format) – 2 (Decimal places) Sold – Yes/No (Data type) – Where or not a car is sold (Description)

 

Purchase table

CustomerID – Text (Data type) – 5 (Field size) – Customer ID (Caption) Primary Key; Foreign Key (Description) VehicleID – Text (Data type) – 5 (Field size) – Vehicle ID (Caption) Primary Key; Foreign Key (Description) SalesRepID – Text (Data type) – 3 (Field size) – Rep ID (Caption) Foreign Key (Description) PurchasePrice – Currency (Data type) – Currency (Format) – Purchase Price (Caption) PurchaseDate – Date/Time (Data type) – Short Date (Format) – Purchase Date (Caption)

 

Customer table

CustomerID – Text (Data type) – 5 (Field size) – Customer ID (Caption) – Primary Key (Description) CustomerFName – Text (Data type) – 20 (Field size) – Customer First (Caption) CustomerLName – Text (Data type) – 30 (Field size) – Customer Last (Caption) CustomerPhone – Text (Data type) – 15 (Field size) – Phone (Caption) CustomerDOB – Date/Time (Data type) – Customer’s Date of Birth (description)

 

SalesRepresentative table

SalesRepID – Text (Data type) – 3 (Field size) – Rep ID (Caption) Primary Key (Description) SalesRepLastName – Text (Data type) – 30 (Field size) – Rep Last Name (Caption) SalesRepFirstName – Text (Data type) – 30 (Field size) – Rep First Name (Caption) SalesRepCellPhone – Text (Data type) – 15 (Field size) – Cell Phone (Caption)

Designate primary key(s) in all 4 tables

* To select multiple attributes as composite PKs in Purchase table, press ‘Ctrl’ and select three attributes, CustomerID, VehicleID, & SalesRepID.

Second, place (insert) appropriate input masks for all attributes containing the date and phone number.

Create the following queries:

  1. [Name the query as Qry1] Create a query that displays which customer has purchased which vehicle. Your query must display vehicle ID, model, class, customer last name, and customer phone number. Sort the results by customer last name in an ascending order

Solution:

SELECT Car.VehicleID, Car.Model, Car.Class, Customer.CustomerLName, Customer.CustomerPhone
FROM Car INNER JOIN (Customer INNER JOIN Purchase ON Customer.CustomerID = Purchase.CustomerID) ON Car.VehicleID = Purchase.VehicleID
ORDER BY Customer.CustomerLName;

  1. [Name the query as Qry2] Create a query that will be used to as a source for ‘contract’. This query should generate information on customer’s last and first names, vehicle’s ID, manufacturer, model, class, purchase price and date as well as a sales rep’s ID, first and last names.

Solution:

SELECT Customer.CustomerFName, Customer.CustomerLName, Car.VehicleID, Car.Manufacturer, Car.Model, Car.Class, Purchase.PurchasePrice, Purchase.PurchaseDate, Purchase.SalesRepID, SalesRepresentative.SalesRepLastname, SalesRepresentative.SalesRepFirstName
FROM SalesRepresentative INNER JOIN (Customer INNER JOIN (Car INNER JOIN Purchase ON Car.VehicleID = Purchase.VehicleID) ON Customer.CustomerID = Purchase.CustomerID) ON SalesRepresentative.SalesRepID = Purchase.SalesRepID;

  1. [Name the query as Qry3] Create a query that shows a list of available cars (i.e., cars that are not sold) that are either sports coupe (S3 class) or manual transmission (e.g., M3, M4, or M5). These cars will be of interest for relatively young customers. This query should show vehicle ID, model, transmission, and MSRP (18% more of the cost of a car the dealership paid while acquiring the vehicle – this is a derived attribute to be prepared using ‘Expression Builder’). There is NO sorting option needed

Solution:

SELECT Car.VehicleID, Car.Model, Car.Transmission, (Car.[Cost]*0.18) AS MSRP
FROM Car;

  1. [Name the query as Qry4] Create a query that displays a total number of cars in each model sold and its average selling (purchasing) price. The query should have vehicle model, a total number of cars sold, & average purchase price sort the result by average purchase price in a descending order.

Solution:

SELECT Car.Sold, Purchase.PurchasePrice, Car.Model, Purchase.VehicleID, Avg([PurchasePrice]) AS AveragePrice
FROM SalesRepresentative INNER JOIN (Customer INNER JOIN (Car INNER JOIN Purchase ON Car.VehicleID = Purchase.VehicleID) ON Customer.CustomerID = Purchase.CustomerID) ON SalesRepresentative.SalesRepID = Purchase.SalesRepID
GROUP BY Car.Sold, Purchase.PurchasePrice, Car.Model, Purchase.VehicleID;

  1. [Name the query as Qry5] Create a query displaying the Rep ID, Rep Last Name, Rep First Name, Vehicle ID, Purchase Price, Cost, Individual Profit, & Commission. The company makes its business by generating profits out of a car sale (Individual Profit) and has a business policy that guarantees its sales representatives 15% of the profit made from a sale of a car as a commission.

Solution:

SELECT SalesRepresentative.SalesRepID, SalesRepresentative.SalesRepLastname, SalesRepresentative.SalesRepFirstName, Car.VehicleID, Purchase.PurchasePrice, Car.Sold, Car.Cost, (Purchase.PurchasePrice – Car.Cost ) AS IndividualProfit, .15 * (Purchase.PurchasePrice – Car.Cost ) AS Commission
FROM SalesRepresentative INNER JOIN (Car INNER JOIN Purchase ON Car.[VehicleID] = Purchase.[VehicleID]) ON SalesRepresentative.[SalesRepID] = Purchase.[SalesRepID];

LEAVE A REPLY

Please enter your comment!
Please enter your name here