Getting the most latest Revision (help please)

[Kookai]

Hi Guys,



I need help i have two tables one to many relationship tblDocumentDrawing & tblDocumentDrawingRev...

And im seriously having trouble getting the most recent revision?

Guys my db is attached someone can help me please???

Thanks

[weekend00]

Didn't find the mdb file.

[Kookai]

im sorry its a different file.... ill attached it again...

[weekend00]

Still could not open file: DBASE 08-20-10.accdb in my Access 2003 saying "unrecognized format"
(I renamed it to DBASE 08-20-10.mdb)

[Kookai]

for a sec ill try to convert it to 2000 verion...

[Kookai]

Hi, i cant save it in earlier version as there object that has none in the latter....dont u have 2007??

[weekend00]

I have only Access 2003

can you use "convert database" to convert it to early version?
In 2003, it is: Tools -> Database utilities -> convert database ->(early versions)

Or just give more detail to see if I can figure out what is in your tables

[weekend00]

I assume your tables are like these:
DocID, DocDetails in table tblDocumentDrawing,
DocID, RevDate in table tblDocumentDrawingRev.
and you want to get docDetails and most recent RevDate.

First, create a query to get the latest RevDate:
select docID, max(RevDate) as RevDate from tblDocumentDrawingRev

Then get the details:
select a.DocID, a.DocDetails, b.RevDate from tblDocumentDrawing as a inner join tblDocumentRev as b on a.DocID=b.docID

[Kookai]

Hi Still Cant convert it.... i will print screen the relationship i have and the tables that is concerned...

[pbaldy]

Does this work for you?

http://www.baldyweb.com/LastValue.htm

[Kookai]

Does this work for you?

http://www.baldyweb.com/LastValue.htm

Actually, im a newbie and cant understand much of what u have sent

[Kookai]

I assume your tables are like these:
DocID, DocDetails in table tblDocumentDrawing,
DocID, RevDate in table tblDocumentDrawingRev.
and you want to get docDetails and most recent RevDate.

First, create a query to get the latest RevDate:
select docID, max(RevDate) as RevDate from tblDocumentDrawingRev

Then get the details:
select a.DocID, a.DocDetails, b.RevDate from tblDocumentDrawing as a inner join tblDocumentRev as b on a.DocID=b.docID

I just had it in print screen,....

[Kookai]

i have tblDocumentDrawing with fields...
DocID, Ref1, Ref2, Subject

and tblDocumentDrawingRev with fields
DocumentDrawingRevID,fkRevisiontransmittal,fkDocum entdrawingID,revisionNo, revdate,revstatus

I want to get the document details which is Ref1,ref2,subject,rev,rev dte, revstatus...

Ref1 & ref2 have many revisions and i want to pull out the most latest one....

[weekend00]

I think your two table are similar to my example ones. So, just change the fields names in my queries and add the fields you want to the result.

[Kookai]

I think your two table are similar to my example ones. So, just change the fields names in my queries and add the fields you want to the result.

hi can you send it to me step by step please.... im going to design query and .....