*Help with SQL Max Query with Criteria

[ironfelix717]

Hi,

Looking for some help with a pretty basic SQL problem. Rather than use DMax with a query, i'd prefer to open a recordset and do it all in one step. If Access allowed query string in the DMax function then my issue would also be solved.



Table:

ID          Version
--------------------------
A60            5
A100          1
A100          2
A102          1
A300          1
A300          2
A60            1
A60            2
A60            3

I need the max version where the ID equals a variable.

For example, if ID = A100, query should return record where version = 2 (max)

If ID = A60, query should return record where version = 5


Thanks for the help!

[June7]

Perhaps you need TOP 1 nested query. Review http://allenbrowne.com/subquery-01.html#TopN

[ironfelix717]

Hi, thanks for the reply.

Nested query would be to much work.

I found the solution. SQL string should be as follows..

sql = "SELECT table1.ID, Max(table1.Version) AS Version FROM table1 GROUP BY table1.ID HAVING table1.ID= '" & ProcID & "';"

Regards,