including another field in a query 2

[tazardous]

I've got a table that I want to extract a subset of data from.
It contains three fields of importantance:

leasekey : the foreign key that ties this table to a parent
effectivedate : the data associated with a lease rate
rateSF : the lease rate per square foot.

What I want to do is query the table to get a set of records showing the current rateSF for each leasekey, limiting the data to the MOST recent leaserate in effect by choosing
the Max(tblRateDetails.EffectiveDate) that is <= NOW().

The SQL below works great, returns exactly the records I need, but fails when I try to include the field rateSF, which I need.

Can anyone help me construct this statement so that i get a set of records that show all three fields?
--------------------------------------------------
SELECT Max(tblRateDetails.EffectiveDate) AS MaxOfEffectiveDate, tblRateDetails.LeaseKey
FROM tblRateDetails
WHERE (((tblRateDetails.EffectiveDate)<=Now()))
GROUP BY tblRateDetails.LeaseKey
ORDER BY tblRateDetails.LeaseKey;
--------------------------------------------------
Thanks
Taz

 

[PHV]

Have you tried this ?
SELECT D.EffectiveDate, D.LeaseKey, D.rateSF
FROM tblRateDetails D INNER JOIN (
SELECT Max(EffectiveDate) AS MaxOfEffectiveDate, LeaseKey
FROM tblRateDetails WHERE EffectiveDate<=Now() GROUP BY LeaseKey
) M ON D.EffectiveDate = M.MaxOfEffectiveDate AND D.LeaseKey = M.LeaseKey
ORDER BY D.LeaseKey;


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[tazardous]

After a very long day (it's 1 AM) I can't tell you how thankful I am for your solution. Yes, it works great. Thank you for taking the time to be helpful. Once again I am amazed at how willing others are to share their knowledge.

Taz