Hi

WinXPPro
Access 2002 SP3

I'm trying to achieve what I thought would be straightforward but I can't seem to find a query that works.

I have table with 3 number columns, the 1st of which (System_ID) is an autonumber / primary key field.

In it, Product_IDs can have several Version_Nos and I want to extract the highest version no for each Product ID.

Some example data


System_ID Product_ID Version_No
------------ ------------ ------------
1 7 1
2 7 2
3 7 3
4 21 1
5 21 2
6 43 1
7 43 2
8 43 3
9 43 4
10 16 1
etc




The results I am trying to achieve are:


System_ID Product_ID Version_No
------------ ------------ ------------
3 7 3
5 21 2
9 43 4
10 16 1



(Apologies for the tabulation above)

I've tried various GROUP BYs, Select Max's and so on but to no avail?

Can someone help please? I'm sure this is pretty easy to do?

Thanks

I have a solution for you here using two queries. You may be able to merge them



qry1

SELECT Table1.Product_ID, Max(Table1.Version_no) AS Max_Version_no
FROM Table1
GROUP BY Table1.Product_ID;

qry2

SELECT Table1.System_id, qry1.Product_ID, qry1.Max_Version_no
FROM qry1 INNER JOIN Table1 ON (qry1.Max_Version_no = Table1.Version_no) AND (qry1.Product_ID = Table1.Product_ID);

hope this helps

You could make 2 queries. The first (Q1) select Product_ID and Version_NO and group by ProductID and MAX VersionNO.

Then in Query2 (Q2), add Q1 and your table and make an INNER JOIN on both ProductID and VersionNO, then add your fields from the table.

JR

Thanks chaps... I shall have a dabble with those ideas... rgds

Thanks chaps... I shall have a dabble with those ideas... rgdsI tested my solution and it gave the results you were looking for.

Thanks Rabbie... Do you know how I might combine the two Queries so that I can use a single SQL statement in my VBA code?

I must admit that when I came up with the idea of "versions" I seemed to recall being able to combine two columns as one searchable key?... thus using 'max' on the combined key would produce the results? It was, however, many, mannnyyy moons ago and I may be fooling myself!

Thanks for your assistance

I will have a look at this after work.

Hi Rabbie

Did you get chance to have a look at combining these queries into one VB SQL call ? I can't seem to get the syntax down correcty....

Many thanks for your help..

Sorry I couldn't get a combined query to work. However if you store qry1 in Queries then you could fire off qry2 from either the Queries window or from code or use that SQL as the record source for a report/form.

Hi

OK, I'll use the two Queries method... ( and, anyway, sometimes combining complex SELECTs ends a support nightmere months later!)

Thanks for your help - much appreciated.

Rgds

Hi

OK, I'll use the two Queries method... ( and, anyway, sometimes combining complex SELECTs ends a support nightmere months later!)

Thanks for your help - much appreciated.

Rgds
Happy to have been of help

Hi Rabbie (and All)

After some dabbling and further research, I got the following SQL call to work... it retrieves the highest version no of each product in a product_lines table.

SELECT Tgt_Tbl.*
FROM Product_Lines AS Tgt_Tbl, [SELECT max(Version_No) as Max_Version,
Product_ID
FROM Product_Lines
GROUP BY Product_ID]. AS maxresults
WHERE Tgt_Tbl.Product_ID=maxresults.Product_ID And Tgt_Tbl.Version_No=maxresults.Max_Version
ORDER BY TGT_Tbl.Name;

Hope it may be of use to someone else.

Thanks for your assistance