Show Top Value in a Joined Query of Two Tables

  • Thread starter Thread starter Larry R Harrison Jr
  • Start date Start date
L

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
 
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
 
Thank you very much! You nailed it!

LRH


Allen Browne said:
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.

Larry R Harrison Jr said:
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
Click to expand...
would
be
the one with the maximum revision number for a given docid.
Click to expand...
Click to expand...
 
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.
Click to expand...

You could try

SELECT * from tblRevisions
WHERE revision = (SELECT max(revision) FROM tblRevisions)
AND DocId = <whatever>
 
Ignore my last post, it should read

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