Query to retrieve only the latest records

[omsoft]

I have the following query in a VBA app.

SELECT tbl_Price.DIV, tbl_Price.Dept, tbl_Price.Section, tbl_Price.EffDate,
tbl_Price.Cost, tbl_Price.Fee, tbl_Price.Price
FROM tbl_Price
WHERE (((tbl_Price.EffDate ) In (SELECT [EffDate] FROM [tbl_Price] As Tmp
WHERE [tbl_Price].[DIV] = 'ABCD'
GROUP BY [DIV],[Dept],[Section],[EffDate] HAVING Count(*)>1 And [Dept] =
[tbl_Price].[Dept] And [Section] = [tbl_Price].[Section] And [DIV] =
[tbl_Price].[DIV])))
ORDER BY tbl_Price.DIV, tbl_Price.Dept, tbl_Price.Section, tbl_Price.EffDate
DESC;

There may be older pricing records and hence I sort by EffDate in descending
order. But what i really want to do is only retrieve records from the date
closest to today. For example below.

Rec # DIV DEPT Section EffDate Cost Fee Price
1 ABCD 1 11 11/10/08 1 .25 1.25
2 ABCD 1 11 11/05/08 1 .2 1.20

In the above example, I only want to get record 1 back and not both back.

Is there a way to do in it using SQL or I have to write VBA code matching
with previous record?

Thanks a bunch.

 

[Jim Burke in Novi]

This might not be the most efficient way to do this, but I think it should
work:

1) Create a query that has a Group By on Div, Dept and Section and gets the
maximum EffDate. This will give you the max date for every combination of
those three fields.

2) Create a 2nd query that joins the first query and the tblPrice table by
Div, Dept, Section and EffDate. This query would then give you every
combination of Div, Dept an Section along with the row that has the maximum
date value. Select whatever the fields are that you need in this 2nd query.