Query For Showing Top Record in Linked Tables

[Larry R Harrison Jr]

I have an Access 97 database with 2 tables, parent one called tblDocuments
and child one named tblRevisions. They are linked with DocID in both tables.

In the revisions table a given DocID can show up numerous times. Another
field named revision will vary in these records.

A query I have will show all instances of a given DocID in the revisions
table. What I want it to do is show the most recent revision, which would be
the one with the maximum revision number for a given docid.

Tips?

LRH

[Larry Linson]

In the Query Builder Grid, set Descending in the Sort Line under Revision
Number, rightclick in the top section (but not on one of the tables) and
choose properties, then set 1 under Top Values. That ought to do it.

Larry Linson
Microsoft Access MVP

"Larry R Harrison Jr" <la*******@cox.net> wrote in message
news:B3Dqb.3110$0K6.2275@fed1read06...

I have an Access 97 database with 2 tables, parent one called tblDocuments
and child one named tblRevisions. They are linked with DocID in both tables.
In the revisions table a given DocID can show up numerous times. Another
field named revision will vary in these records.

A query I have will show all instances of a given DocID in the revisions
table. What I want it to do is show the most recent revision, which would be the one with the maximum revision number for a given docid.

Tips?

LRH

[Larry R Harrison Jr]

"Larry Linson" <bo*****@localhost.not> wrote in message
news:j8****************@nwrddc02.gnilink.net...

In the Query Builder Grid, set Descending in the Sort Line under Revision
Number, rightclick in the top section (but not on one of the tables) and
choose properties, then set 1 under Top Values. That ought to do it.

I did that, but now it only shows it for one doc ID at the time rather than
doing this for all of them.

[Larry Linson]

"Larry R Harrison Jr" wrote

I did that, but now it only shows it for one
doc ID at the time rather than doing this
for all of them.

OK, I didn't read carefully enough.

First, make a totals query: Group By DocID, Max of Revision

Then, create a query including the Document table, the totals query you just
created, and the Revision table. Join the Totals Query and the Document
table on Doc ID, and Join the Totals Query and the Revisions table on Doc ID
and (MaxOfRevision to Revision).

Pull down any other Document and Revision information.

Follow up here if that does not do what you want. Obviously I don't have
your tables, but I tested something similar using the Customers and Orders
tables of the sample Northwind.MDB.

Larry Linson
Microsoft Access MVP