I was trying to figure out a way to consolidate data to pick the most current Number and Number2 based on its corresponding Max(Date_Number).

DATA

I was trying to figure out a way to consolidate data to pick the most current Number and Number2 based on its corresponding Max(Date_Number).

DATA

EXPECTED

Tried making separate tables for each to find the max(date_number) and max(date_number2) but couldn't pull back the corresponding number or number 2 that was next to the max date.

• sql
• ms-access

1 Answer 1

You can either

• build two queries, each using a nested TOP N per group structure (http://allenbrowne/subquery-01.html#TopN), or
• build two queries, each with nested aggregate query to get max values that INNER JOINs to sample table with compound join on Employee and date fields.

In either case, add INNER JOIN resulting two queries to each other or LEFT JOIN to Employees table if you want more employee data. Also, if an employee might not have a record for either number set, JOIN to Employees table where each employee must have a single record.

Using TOP N:

SELECT Employee, Date_Number, Number 
FROM Data 
WHERE ID IN (SELECT TOP 1 ID 
             FROM Data AS Dupe 
             WHERE Dupe.Employee = Data.Employee 
             ORDER BY Dupe.Date_Number DESC);

SELECT Employee, Date_Number2, Number2 
FROM Data 
WHERE ID IN (SELECT TOP 1 ID 
             FROM Data AS Dupe 
             WHERE Dupe.Employee = Data.Employee 
             ORDER BY Dupe.Date_Number2 DESC);

Using aggregation:

SELECT Data.Employee, Data.Date_Number, Data.Number 
FROM Data 
INNER JOIN (SELECT Employee, Max(Date_Number) AS MDN 
            FROM Data 
            GROUP BY Employee) AS Q 
      ON (Data.Employee=Q.Employee) AND (Data.Date_Number = Q.MDN);

SELECT Data.Employee, Data.Date_Number2, Data.Number2 
FROM Data 
INNER JOIN (SELECT Employee, Max(Date_Number2) AS MDN 
            FROM Data 
            GROUP BY Employee) AS Q 
      ON (Data.Employee=Q.Employee) AND (Data.Date_Number2 = Q.MDN);

Final query (if each employee is sure to have a record for each number set):

SELECT Query1.*, Query2.* 
FROM Query2 
INNER JOIN Query1 ON Query2.Employee = Query1.Employee;

Otherwise, JOIN to Employees table

SELECT Employees.EmployeeNum, 
       Query1.Date_Number, 
       Query1.Number, 
       Query2.Date_Number2, 
       Query2.Number2
FROM (Employees LEFT JOIN Query1 ON Employees.EmployeeNum = Query1.Employee) 
LEFT JOIN Query2 ON Employees.EmployeeNum = Query2.Employee;