Effective date query

[KD]

Could someone be so kind to tell me if there is a more elegant way of
determining the "effective date" compared to my beginners attempt
below:

SELECT *
FROM tbl_Rates
WHERE (tbl_Rates.EffectiveDate =
(SELECT MAX(Temp.EffectiveDate)
FROM tbl_Rates AS Temp
WHERE (Temp.EffectiveDate <= #08/08/2010#
AND Temp.Payroll_Company = "XXXX PTY LTD"
AND Temp.Payroll_Location = "Cleaning"
AND Temp.OnCost_type = "PayrollTax"))
AND tbl_Rates.Payroll_Company = "XXXX PTY LTD"
AND tbl_Rates.Payroll_Location = "Cleaning"
AND tbl_Rates.OnCost_type = "PayrollTax");

 

[Ken Snell]

Your posted query SQL statement is fine if you want to bring back all the
fields from tbl_Rates for the "effective date" record.

 

[John Spencer]

You could make it more general, but that may cost you in terms of speed.

SELECT *
FROM tbl_Rates
WHERE (tbl_Rates.EffectiveDate =
(SELECT MAX(Temp.EffectiveDate)
FROM tbl_Rates AS Temp
WHERE Temp.EffectiveDate <= #08/08/2010#
AND Temp.Payroll_Company = tbl_Rates.Payroll_Company
AND Temp.Payroll_Location = tbl_Rates.Payroll_Location
AND Temp.OnCost_type = tbl_Rates.OnCost_type)
AND tbl_Rates.Payroll_Company = "XXXX PTY LTD"
AND tbl_Rates.Payroll_Location = "Cleaning"
AND tbl_Rates.OnCost_type = "PayrollTax");


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[KD]

Thanks, Ken & John