two date fields criteria

J

J.J.

hi
i have in my query two date fields(Date_1 and Date_2)
the criteria is date_2 > date_1. in this case naturally i get multiple
records but i need only first bigger date_2 to be shown.
is there a way to do it?
many thanks
J.J.
 
M

Michel Walsh

Hi,


SELECT a.f1, a.dateTime, MIN(b.dateTime)
FROM tableName as a LEFT JOIN tableName As b
ON a.f1=b.f1 AND a.dateTime < b.DateTime


The tablename aliased as b can be another table too.


Hoping it may help,
Vanderghast, Access MVP
 
J

J.J.

I don't get it?
i have table1.date1 and table2.date2
what do you mean with a.f1?
tia
JJ
 
M

Michel Walsh

Hi,


f1 is a field to make a "match" or "group" other than by "date" (such
as clientID, accountID, ItemID, ... ) If there is none, just remove that
condition (I also originally missed the GROUP BY clause, at the end of the
statement) :



SELECT a.f1, a.date1, MIN(b.date2)
FROM table1 as a LEFT JOIN table2 As b
ON a.f1=b.f1AND a.date1 < b.date2
GROUP BY a.f1, a.date1

or, if there is no candidate for field "f1" , just:

SELECT a.date1, MIN(b.date2)
FROM table1 as a LEFT JOIN table2 As b
ON a.date1 < b.date2
GROUP BY a.date1





Hoping it may help,
Vanderghast, Access MVP
 
Top