pick one out of three with the smallest date difference

[Lifeseeker]

Hi,

I would like to have a query that pulls any ONE of the three tests stored in three different columns based on the date difference being the smallest.

So column A, column B and column C, and they all contain date/time.

if any one of the columns has value, use it and if there are two values picked up in the column C, then I would like the query to compare column C with column D and get me the one where the date difference is the smallest.

Does this make sense?

How should I go about doing this?

Thank you

 

[Guus2005]

You have to split this problem up into smaller pieces.
The date difference being the smallest: DateDiff("d", Date1, Date2) as DateDifference
Add this column to your query:

select A, B, C, DateDiff("d", A, B) as DateDifferenceAB from Table1

Do this for all the difference you require and save the query (Query1)

Use this query to work from next. This way you have split the problem into smaller pieces.

When you're done and you feel the need to put it into one big query that's your call..

HTH