Table to show latest record

[Damien W]

Hi all, I am having a bit of trouble getting my head
around a query. I want to show only the latest revision
to a document in a document register I'm building
I have 2 tables, Table 1 has:

DocID DocNumber DocRev DocDescription

Table 2 has:

RevID DocNumber DocRev RevDetails RevDate

So all revision details are entered in table 2, so I can
keep a history of the revisions. idealy I would like the
query to show results of:

DocID DocNumber DocRev(latest) DocDescription

I recall a function that will allow the sorting of data
by "Max" somehow but I do not remember where I found it.
It may even be beneficial to do an Append query to make
table 1 show the most recent revision but thats beyond me
at the moment.
I hope there's enough info for someone to review,

Thanks in advance,

Damien W.

 

[Bob Gardner]

Wouldn't Table1 already have the latest revision record
for each document if Table2 contains subrecords of Table1?

 

[SteveS]

I would have the tables designed a little different.

Table1 (documents)
DocID
DocNumber
DocDescription

Table2 (revisions)
RevID
DocID_FK
DocRev
RevDetails
RevDate


The relationship would be

Table1.DocID ----> Table2.DocID_FK
1 ----> Many


And the query would be

Table1.DocID, Table1.DocNumber, Table1.DocDescription,
Table2.DocRev, Table2.RevDate, Table2.RevDetails

You could use Max(DocRev) or Max(RevDate) in the criteria
to get the latest revision.

HTH

Steve

 

[Damiem W]

Thanks Bob, yes I expect it will have the latest due to
the relationship, my problem is that I need to make sure
that the latest record is displayed (somehow). Whether my
colleagues refer to the infomation directly from the
table or I make queries to view the data I would like to
ensure that the first revision that they see is the
latest.
I once worked on a similar project in Access that had
been upgraded to run on SQL Server with an Access front
end. There was a parameter called MAX, I think, that you
could sort by, might have been part of the MAX/MIN thing.

I hope this is a clearer picture.

Regards,
Damien W