Hi

One of my tables contains a list of documents IDs their revision and the date issued.

From this table i'm trying to select the latest revision of the document.

The table has as a primary key made fron the Document ID and Revision columns.

I am using Access 2002

Is the date issued your latest document date? or if is any other date like create date you can use distinct and max

Here is an example using the customers and orders table in Northwind Database

SELECT DISTINCT Customers.CustomerID, Customers.CompanyName, Customers.City, Customers.Country, Max(Orders.OrderDate) AS MaxOfOrderDate
FROM Customers RIGHT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
GROUP BY Customers.CustomerID, Customers.CompanyName, Customers.City, Customers.Country
HAVING (((Max(Orders.OrderDate)) Between #1/1/1997# And #12/31/1997#));

Thanks Jemacc

The date is not unique to the record but it is always later than the earlier revisions.

I tried your Select Statement transposing my Table and field names but it selected all the recors in the table.


To make my problem a bit clearer here is sample data

Document Revision Table
DocID Rev Date
Dwg1 A 9/1/2004
Dwg1 B 9/5/2004
Dwg3 C 9/15/2004
dwg4 A 9/1/2004
dwg4 C 9/15/2004

Required result from Query

DocID Rev Date
Dwg1 B 9/5/2004
Dwg3 C 9/15/2004
dwg4 C 9/15/2004

Try this
N=numbers of records

SELECT TOP N DocID,Rev, Date
FROM Revision_Table
GROUP BY DocID,Rev, Date
ORDER BY DocID,Rev, Date desc