getting the latest version (MAX) of multiple distinct documents

[kismet]

I am working on this document library and I want to display only the latest versions of the available document titles. For example, under the Analysis document type, there are the Hardware document title (Analysis_Hardware) and the Software document title (Analysis_Software). These document titles have multiple version numbers to them.

I've already got the SQL statement that looks for that record(document title) which has the highest version number. My problem is, I think it only works for scenarios where there is just one existing document title under a document type. I need an SQL statement that returns the latest version of ALL existing document titles under a document type. Meaning, the latest versions of Analysis_Hardware and Analysis_Software should appear. Please help! =)

This is my SQL statement:
SELECT Doctype, Title, Version, ReleaseDate FROM Document
WHERE Version = (SELECT MAX(Version) FROM Document);

How can I manipulate this statement so as to solve my problem? Help would be greatly appreciated. Thank you in advance! =)

 

[TomCologne]

Try the following in 2 new queries:

1.) qryMaxDocument

SELECT Max(tblDocument.Version) AS MaxOfVersion, tblDocument.Doctype     
FROM tblDocument
GROUP BY tblDocument.Doctype;

2.) qryMaxDocDetail

SELECT DISTINCT qryMaxDocument.MaxOfVersion, qryMaxDocument.Doctype, tblDocument.ReleaseDate , tblDocument.Title
FROM qryMaxDocument INNER JOIN tblDocument ON (qryMaxDocument.Doctype = tblDocument.Doctype) AND (qryMaxDocument.MaxOfVersion = tblDocument.Version);

This should be roughly what you're looking for, if my interpretation of your table description is correct.
Your table "Document" = "tblDocument".

This may need some fine tuning as I just modified one of my queries.
Let me know how it works, please.

You're not alone,

TomCologne

 

[kismet]

tom, it's not working. I think there's something wrong with qryMaxDocument. An error keeps popping up saying "You tried to execute a query that does not include the specified expression 'Doctype' as part of an aggregate expression." What do I do?

big thanks for you help! =)

 

[TomCologne]

This could work:

SELECT Max(tblDocument.Version) AS MaxOfVersion, tblDocument.Title, tblDocument.Doctype, Max(tblDocument.ReleaseDate) AS MaxOfReleaseDate
FROM tblDocument
GROUP BY tblDocument.Title, tblDocument.Doctype;

Sample table:

Doctype Title Version ReleaseDate
-----------------------------------------------------------
Analysis_Hardware Example1 1.001 1/2/2002
Analysis_Hardware Example1 1.002 1/2/2003
Analysis_Hardware Example2 2003.2 2/1/2003
Analysis_Hardware Example2 2003.4 3/1/2003
Analysis_Hardware Example3 30003 12/31/2002
Analysis_Software Example1 1.001 1/3/2002
Analysis_Software Example1 1.002 1/3/2003
Analysis_Software Example2 2.2002 2/2/2002
Analysis_Software Example2 3.2002 3/3/2003
Analysis_Software Example3 30003 4/4/2003
Analysis_Software Example3 30004 5/5/2003
Analysis_Software Example4 11.305 6/6/2002

The qry based on the sample table should return 7 records.
If you change, ie "Example1" to "Example 1", the result will be different, of course.

My first post's original qry was actually based on slightly different circumstances and my modification rather quick'n'dirty.

Let me know,

TomCologne

 

[kismet]

It worked!!! Thanks a lot!!! =)