latest date

[Mary Pitts]

I have a table that keeps all of the student information
and table the keeps the rank information for each student
and a table that kepts all the class information for each
student. The rank table may have several ranks and
classes per student. If I select a class I need the rank
that is the closest date to the class date. Just one date.

 

[Michel Walsh]

Hi,

I assume you have two tables, one with the Classes and the Dates,
ClassesDates, and one with Dates and Ranks, DatesRanks.


SELECT a.*
FROM DatesRanks As a
WHERE a.theDate <= ( SELECT b.theDate
FROM ClassesDates As b
WHERE b.Class= [SuppliedClassNumber] )


will return all the records from DatesRanks that occur before the date
associated with the class. To get just the rank of the "closest" record:


SELECT TOP 1 a.rank
FROM DatesRanks As a
WHERE a.theDate <= ( SELECT b.theDate
FROM ClassesDates As b
WHERE b.Class= [SuppliedClassNumber] )
ORDER BY a.theDate DESC



Hoping it may help,
Vanderghast, Access MVP