Choosing most recent date

[hatemma]

I need help please. I have two tables that list results of tests on the same
group of patients done on specific dates. I have linked the two tables with
patient hospital number. I can generate a query that list the two test that
are done on the same day by writing in the criteria [date test 1]=[date test
2]. My problem is that some times, test two were not done on the same date(
either befor or after). I need to generat a query to select test two that was
done on the same day of test one and if there is no test on that day to
select the results from the closest date. I hope that you can help and sorry
for the long question.

 

[KARL DEWEY]

There is probably a better way but this works. You will need to change the
table and field names. There are three queries.
Closest Date --
SELECT [Change Requet-1].y, [Change Requet-1].[Date close],
Min(Abs(DateDiff("d",[Change Requet-1].[Date close],[Change Requests].[Date
close]))) AS Expr1, [Change Requests].[Date close]
FROM [Change Requests] RIGHT JOIN [Change Requet-1] ON [Change Requests].y =
[Change Requet-1].y
GROUP BY [Change Requet-1].y, [Change Requet-1].[Date close], [Change
Requests].[Date close]
ORDER BY [Change Requet-1].y, Min(Abs(DateDiff("d",[Change Requet-1].[Date
close],[Change Requests].[Date close])));

ClosestDate-1 --
SELECT [Closest Date].y, [Closest Date].[Change Requet-1].[Date close],
Min([Closest Date].Expr1) AS MinOfExpr1
FROM [Closest Date]
GROUP BY [Closest Date].y, [Closest Date].[Change Requet-1].[Date close];


ClosestDate-2 --
SELECT [Closest Date].y, [Closest Date].[Change Requet-1].[Date close],
[Closest Date].Expr1, [Closest Date].[Change Requests].[Date close]
FROM [ClosestDate-1] INNER JOIN [Closest Date] ON ([ClosestDate-1].y =
[Closest Date].y) AND ([ClosestDate-1].[Date close] = [Closest Date].[Change
Requet-1].[Date close]) AND ([ClosestDate-1].MinOfExpr1 = [Closest
Date].Expr1);