two date fields criteria

  • Thread starter Thread starter J.J.
  • Start date Start date
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.
 
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
 
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
 
Back
Top