Selecting the closest date from a list of records

user_1870218 0
Viewed 44

enter image description here

enter image description here Hi All,

The question I have is regarding the two highlighted rows in the attached picture. The results are from a query made on two tables.

Columns 1 - 7 are from one table Columns 8 - 13 are from another.

As you can see I have two entries for ID 1. This is because in the 2nd table there are two records relating to employee 693.

I only ever want one record per ID, the criteria is that result used from the second table should be the one that has the DateUpdated field (less than or equal to) and closest to the transactionDate in column 7.

Any ideas on how I go about doing this?

UPDATE* Ok thanks to the first suggestuin my results table now looks like the 2nd picture. I have a column that shows the differences between the two dates. So how do I now get the results to only show the row with the lowest number, for each ID?

UPDATE***

KR

Chris

ms-access
1 Answers

Use an aggregating query with:

Min(Abs(DateDiff("d", [Transaction], [Date Updated]))) As NearestUpdateDate

and Group By the remaining fields.

It could go like this:

SELECT 
    tblEmployee.Id, 
    tblEmployee.EmployeeId, 
    tblEmployee.Transaction, 
    Min(Abs(DateDiff("d",[Transaction],[Date Updated]))) AS MinDiff
FROM 
    tblEmployee 
INNER JOIN 
    tblEmployeeWages ON 
        tblEmployee.EmployeeId = tblEmployeeWages.EmployeeId
GROUP BY 
    tblEmployee.Id, 
    tblEmployee.EmployeeId, 
    tblEmployee.Transaction;

Save this as qdyEmployeeDate. Then use this:

SELECT 
    tblEmployee.Id, 
    tblEmployee.EmployeeId, 
    tblEmployee.Transaction, 
    Min(tblEmployeeWages.[Date Updated]) AS DateUpdated
FROM 
    (tblEmployee 
INNER JOIN 
    qdyEmployeeDate ON 
        tblEmployee.Id = qdyEmployeeDate.Id) 
INNER JOIN 
    tblEmployeeWages ON 
        tblEmployee.EmployeeId = tblEmployeeWages.EmployeeId
WHERE 
    (tblEmployee.Transaction = DateAdd("d",[MinDiff],[Date Updated])) 
    OR 
    (tblEmployee.Transaction=DateAdd("d",-[MinDiff],[Date Updated]))
GROUP BY 
    tblEmployee.Id, 
    tblEmployee.EmployeeId, 
    tblEmployee.Transaction;

to get:

enter image description here

Now you have the Ids and the two dates to use. This query you can use in a new query with outer joins to the tables to retrieve the remaining fields.

Note please, that you have two employee records for 693 both having the same two update dates each having the same date difference to these, thus you'll get the same update date for both employee records. That you must sort out yourself.

user_35272970
Related