Selecting single (highest) instance of joined tables

[Collizz]

Hi,

I have returned to Access and SQL after some time away and cannot for the life of me remember how to do something which should be easy enough, if only my little grey cells were operating efficiently ...

I have two tables; one for documents, the other for versions of those documents (simplified field structure):

tblDocs - docNum [PK], docName
tblVers - docNum + versNum + revNum [PK], lastModDate

So, given the following tblDocs data:

1001 | Document One
1002 | Document Two
1003 | Document Three

and the related tblVers data (shown in descending key sequence):

1001 | 1 | 2 | 11/12/13 <
1001 | 1 | 1 | 09/12/13
1001 | 0 | 1 | 01/01/13
1002 | 1 | 1 | 11/11/13 <
1003 | 2 | 0 | 12/12/13 <


1003 | 1 | 1 | 02/02/13

How do I end up with a result set containing just the latest issues (indicated above with '<')?

I've tried DISTINCT, MAX, etc. and believe it is some kind of JOIN structure I may need?

Thanks a lot for any help.

[pbaldy]

Does this work for you?

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

[Collizz]

Paul,

Thanks for the suggestion, but I don't think you grasped what I'm looking to do.

I have two related tables, not one, as stated.

The query I have now returns the following (using my example data):

1001 | 1 | 2 | 11/12/13 <
1001 | 1 | 1 | 09/12/13
1001 | 0 | 1 | 01/01/13
1002 | 1 | 1 | 11/11/13 <
1003 | 2 | 0 | 12/12/13 <
1003 | 1 | 1 | 02/02/13

I am looking to see only the rows I have arrowed above (3 rows in total), comprising the primary key from tblDocs (e.g. 1001) and the highest version + revision (e.g. 1 + 2) from tblVers:

1001 | 1 | 2 | 11/12/13
1002 | 1 | 1 | 11/11/13
1003 | 2 | 0 | 12/12/13

Any other ideas?

[pbaldy]

I still think that technique will work. Can you post a sample db here?

[Collizz]

I still think that technique will work. Can you post a sample db here?

It really is pretty simple. I have one table, tblDocs, keyed on docNum (set to Required=Yes, Indexed = Yes, no duplicates). I have a second table, tblVers, keyed on docNum+versNum+revNum (all 3 are set to Required=Yes, Indexed = Yes, Duplicates OK)

I want to end up with the following 3 records in my result set (Document Number + Highest Version Number + Revision Number | Last Mod Date, etc.):

1001 | 1 | 2 | 11/12/13 ...
1002 | 1 | 1 | 11/11/13 ...
1003 | 2 | 0 | 12/12/13 ...

Can anyone tell me how I can achieve? It would seem to be a typical query, after all.

Thanks

[rpeare]

I'd do this in two queries

1.

SELECT DocNum, Max(VersNum) as MaxVers FROM tblVers GROUP BY DocNum

This should give you a list of the highest version number for each docnum

2.

Link this query to your version table through the docnum and versnum, make it an aggregate query (look for the sigma button on the toolbar, looks like a capital E) and put MAX in the revnum field

ALTERNATELY

If you are 100% certain that your revision date is being recorded correctly you can perform the same type of query using the date field.

[rpeare]

One more note, the MAX function will only work if the version and revision numbers are actually numbers and not text fields. You may get inconsistent results if you are using max/min on text field that looks numeric but you can handle that with the clng or cint function if the field is always numeric in appearance. If you start to use 1a, 2b (alpha and numeric) values you may have to fiddle with it a bit more.

[Collizz]

One more note, the MAX function will only work if the version and revision numbers are actually numbers and not text fields. You may get inconsistent results if you are using max/min on text field that looks numeric but you can handle that with the clng or cint function if the field is always numeric in appearance. If you start to use 1a, 2b (alpha and numeric) values you may have to fiddle with it a bit more.

Thanks, rpeare, I'll try that. It's so crazy, it might just work ...

All "...Num" fields are indeed numeric, so I'll give it a whirl.

[Collizz]

Hi rpeare

I can't see any indication of an aggregate function; the only sigma sign is a standard 'total' function.

Can you please let me know what format the SQL statement needs to take, to end up with the result set I'm looking for in my original posting? My VBA code sets the listbox source to a SQL query, which works fine, but returns all docNum+versNum+revNum combinations. I just need the SQL to bring in only the latest tblDocVers.modDate valued row per docNum+versNum+revNum.

Thanks

[Collizz]

Paul

I just wanted to say 'thanks' for your help. I did use the double query to produce the list I need. I just need now to figure out how to make the listbox source equal to a combination of the two queries ...

Also, if somebody can tell me how to mark this post 'closed' it would be appreciated!

[pbaldy]

Happy to help. I would think you could make the second query the listbox row source. You can mark the thread solved in a Thread Tools at the top of the thread.