Table to show latest record

  • Thread starter Thread starter Damien W
  • Start date Start date
D

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.
 
Wouldn't Table1 already have the latest revision record
for each document if Table2 contains subrecords of Table1?
 
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
 
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
 
Back
Top