One Register, 4,300 Complaints
At a 60-seat customer-support centre, every complaint is logged in a single worksheet called Register with columns Ticket No, Date, Customer Name, Mobile, City, Product, Agent Name, Extension, Category, Status, Resolution and Close Date - 4,300 rows after 14 months. Two rows share Ticket No 2187 because a reopened complaint was entered again. The same customer appears as 'R. Meena', 'R Meena' and 'Meena R', with mobile numbers written as 9876543210, +91 98765 43210 and 098765-43210. Agent extension is typed by hand, so one agent appears as 214, 0214 and Ext. 214. Some complaints are tagged 'Billing / Technical' in one Category cell. Managers now want three reports: tickets handled per agent per week, average days to close, and customers who complained three or more times. A VLOOKUP built on the mobile number returns #N/A for many rows.
1. A VLOOKUP on the mobile number returns #N/A for many rows. What is the first data problem to fix, and why is the mobile number a poor primary key for this register?
Read the worked answer
The lookup fails because the same number is stored in different forms, often in different types: 9876543210, +91 98765 43210, 098765-43210, some cells numeric and some text with a leading apostrophe or a trailing space. VLOOKUP compares exact values, so 9876543210 never equals +91 98765 43210. TRIM alone will not help: you must strip spaces, hyphens, the +91 prefix and any leading zero, keep the last ten digits, and force a single type. A helper column such as =RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(C2,"0")," ",""),"-",""),"+91",""),10) followed by Convert to Number does the job, or clean the data once at import.
Why not the primary key: a key must be unique, stable and never null. A mobile number is none of these - it can be shared by a family, reassigned by the operator, changed by the customer, mistyped, or left blank. Use a system-generated CustomerID as the key and keep the cleaned mobile as an ordinary indexed attribute; lookups then run on CustomerID.
2. Propose a normalised set of tables for this register, with keys, and say where the 'Billing / Technical' field must go.
Read the worked answer
Customer(CustomerID PK, Name, Mobile, City) - one row per person, however the name was typed. Agent(AgentID PK, AgentName, Extension) - the extension is stored once, not retyped on every ticket. Ticket(TicketID PK, TicketNo, OpenDate, CustomerID FK, AgentID FK, Status, Resolution, CloseDate). TicketNo cannot be the primary key because 2187 appears twice; keep it as a business reference and give each row a surrogate TicketID, or use the composite (TicketNo, Attempt) if reopenings must be tracked. Category(CategoryID PK, CategoryName). TicketCategory(TicketID FK, CategoryID FK) with a composite primary key.
The 'Billing / Technical' cell violates first normal form: two values in one field cannot be filtered, counted or indexed reliably, and every new pairing would need a new string. Split it into two rows in TicketCategory. If one category must be flagged as primary, add a column for that - never go back to a delimited string.
The register also repeats agent details on every ticket (transitive dependency) and customer details on every ticket. That redundancy is exactly what produced the spelling variants and the three versions of extension 214.
3. Describe the query for average days to close per agent, and explain how open tickets must be handled so the figure is not misleading.
Read the worked answer
SELECT a.AgentName,
COUNT(t.TicketID) AS ClosedTickets,
AVG(DATEDIFF('day', t.OpenDate, t.CloseDate)) AS AvgDays
FROM Ticket t
JOIN Agent a ON a.AgentID = t.AgentID
WHERE t.CloseDate IS NOT NULL
GROUP BY a.AgentName
ORDER BY AvgDays DESC;
Use DateDiff('d', ...) in Access and DATEDIFF(day, ...) in SQL Server. Do not store a Days column in the table; calculate it in the query so it cannot go stale.
Open tickets: AVG ignores rows where CloseDate is NULL, so an agent with 40 quick closures and 30 still-open tickets would look excellent. Never substitute 0 or today's date for the NULL - that silently changes the metric. Report open tickets separately (count where CloseDate IS NULL, with their age from OpenDate) and label the figure 'average over closed tickets only'.
Before comparing agents, also decide whether a reopened ticket counts once or twice, and check that no CloseDate is earlier than its OpenDate.