find value closest to 0

Question

I have searched for the answer to my query all afternoon and I'm totally stuck so hoping someone may be able to shed some light on where I am going wrong.

My first table is:

Blood Samples
PatientNumber        Date Sample Collected
------------------------------------------------

There can be more than one record in this table per patient as they may have provided blood samples on more than one date.

I have another table (Lab Tests) listing a lab test that may have been performed on different dates for each patient:

Lab Tests
PatientNumber     TestDate
----------------------------------

What I am trying to do is find the closest blood sample date to each lab test date.

I have created a query (qry_All Samples and Tests) joining on PatientNumber between the two tables. So in this query I have the fields:

Patientnumber,
Date Sample Collected (from Blood Samples),
Test Date (from Lab Tests)

As this is a many to many join the query is returning a lot of superfluous results.

I have then added an expression into a new column called "Days Difference between Sample date and Test date" to show how many days are between the Date Sample Collected and the Test Date in each record:

DateDiff("d",[Blood Samples]![Date Sample Collected],[Lab Tests]![Test Date])

Before adding the expression above to my query (qry_All Samples and Tests) I tried running the following SQL code to locate the nearest Date Sample Collected to Test Date but all it did was return exact matches between the dates:

SELECT Patientnumber, [Date Sample Collected], [Test Date]
FROM [qry_All Samples and Tests]
WHERE abs(datediff("d",[Date Sample Collected],[Test Date]))
= ( select min(
abs(datediff("d",[Date Sample Collected],[Test Date]))
)
from [qry_All Samples and Tests] );

I know that there are some blood samples where the nearest Test Date is not on the same date as the Date Sample Collected so I know the SQL above is missing a load of records when it runs.

After adding the "Days Difference between Sample date and Test date" column to my query (qry_All Samples and Tests) I have then tried to find a way to only return the record where this column is closest to 0 (days) but every post I have come across searches in one direction from the value - either before or after the value. My "Days Difference between Sample date and Test date" column contains both negative and positive values so I can't use min or max with this one. I need to find the closest Test Date that occurs either before or after the Date Sample Collected.

I realise there may be Test Dates that are equal distance either side of the Date Sample Collected but that's ok. I just need to get to the stage where I'm returning what should be the closest Test Date to the Date Sample Collected.

I hope all of this makes sense and I am very grateful for any advice.

Answer 1

OK so I've worked it out...

This is my first query (qry_All Samples and Tests):

SELECT [Blood Samples].Patientnumber, [Blood Samples].[Date Sample Collected], [Lab Tests].StudyNumber, [Lab Tests].[Test Date], Abs(DateDiff("d",[Blood Samples]![Date Sample Collected],[Lab Tests]![Test Date])) AS [Date difference]
FROM [Blood Samples] INNER JOIN [Lab Tests] ON [Blood Samples].Patientnumber = [Lab Tests].StudyNumber
WHERE ((([Lab Tests].[Test Date]) Is Not Null));

The major difference with the above is that I have used the expression Abs(DateDiff("d",[Blood Samples]![Date Sample Collected],[Lab Tests]![Test Date]) not just (DateDiff("d",[Blood Samples]![Date Sample Collected],[Lab Tests]![Test Date]). Doing this means that the numbers returned in this new column (Date Difference) are all positive.

This then allowed me to search for the min value in the Date Difference column in a new query (qry_Closest Viral Load to Sample) which returned records closest to 0 in Date Difference column:

SELECT [qry_All Samples and Tests].Patientnumber, [qry_All Samples and Tests].[Date Sample Collected], Min([qry_All Samples and Tests].[Date difference]) AS [MinOfDate difference]
FROM [qry_All Samples and Tests]
GROUP BY [qry_All Samples and Tests].Patientnumber, [qry_All Samples and Tests].[Date Sample Collected];

I was then able to link this new query back to the original one (qry_All Samples and Tests) to get the full set of data fields I need:

SELECT [qry_All Samples and Tests].Patientnumber, [qry_All Samples and Tests].[Date Sample Collected], [qry_All Samples and Tests].[Test Date], [qry_All Samples and Tests].[Date difference]
FROM [qry_Closest Viral Load to Sample] INNER JOIN [qry_All Samples and Tests] ON ([qry_Closest Viral Load to Sample].[MinOfDate difference] = [qry_All Samples and Tests].[Date difference]) AND ([qry_Closest Viral Load to Sample].[Date Sample Collected] = [qry_All Samples and Tests].[Date Sample Collected]) AND ([qry_Closest Viral Load to Sample].Patientnumber = [qry_All Samples and Tests].Patientnumber)
ORDER BY [qry_All Samples and Tests].Patientnumber, [qry_All Samples and Tests].[Date Sample Collected];

I hope this helps anyone in the future trying to find the closest value to 0 and thanks to Gustav for providing a possible solution to my problem

Answer 2

It seems like you just need to filter on Patientnumber:

SELECT 
    Patientnumber, 
    [Date Sample Collected], 
    [Test Date]
FROM 
    [qry_All Samples and Tests]
WHERE 
    Abs(DateDiff("d",[Date Sample Collected],[Test Date])) =

    (Select Min(Abs(DateDiff("d",T.[Date Sample Collected],T.[Test Date])))
    From [qry_All Samples and Tests] As T
    Where T.Patientnumber = [qry_All Samples and Tests].Patientnumber);

However, as one date difference may match that for another set of dates for the same patientnumber, you may have to run the output through yet a query to fit your purpose.