Show Top Value in a Joined Query of Two 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

 

[Allen Browne]

If you don't mind a read-only result, create a query into tblDocuments, and
type this subquery into the Field row of the query design grid:
MaxDoc: ( SELECT Max(DocID) FROM tblRevisions WHERE tblRevisions.DocID =
tblDocuments.DocID )

If you just want to show the most recent revision number on a form, you
could use this in the Control Source of a text box:
=DMax("DocID", "tblRevisions", "DocID = " & Nz([DocID], 0))

More alternatives:
http://www.mvps.org/access/queries/qry0020.htm

 

[Larry R Harrison Jr]

Thank you very much! You nailed it!

LRH

If you don't mind a read-only result, create a query into tblDocuments, and
type this subquery into the Field row of the query design grid:
MaxDoc: ( SELECT Max(DocID) FROM tblRevisions WHERE tblRevisions.DocID =
tblDocuments.DocID )

If you just want to show the most recent revision number on a form, you
could use this in the Control Source of a text box:
=DMax("DocID", "tblRevisions", "DocID = " & Nz([DocID], 0))

More alternatives:
http://www.mvps.org/access/queries/qry0020.htm

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

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.

 

[John]

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.

You could try

SELECT * from tblRevisions
WHERE revision = (SELECT max(revision) FROM tblRevisions)
AND DocId = <whatever>

 

[John]

Ignore my last post, it should read

SELECT * from tblRevisions
WHERE revision = (SELECT max(revision) FROM tblRevisionsWHERE DocId =