Max of a set of records

[nogreatnamesleft]

I have data in columns like the following:
Request|Version
127|1
127|2
127|3
4856|1
4856|2

I want to display only the latest version in my query (127|3, 4856|2). I am
having difficulty figuring out the right way to do this. Any suggestions?

 

[vanderghast]

SELECT request, MAX(version)
FROM tableName
GROUP BY request


in SQL view (replace tableName with the real table name). You can then
switch in graphic view to see how you could have done it, graphically.


Vanderghast, Access MVP.

 

[nogreatnamesleft]

That would work except that I have other fields in the query which 'are not
part of an aggregate function.' So if I don't use 'Group By' on all the other
fields i get the 'not part of an aggregate function' error. If I do use
'Group By' on all the other fields I get multiple versions per request
because of different values per Request in the other fields. So what do I
have to do to the other fields? Do I need a subquery first to get the latest
version then add the other fields?

 

[KARL DEWEY]

I have other fields in the query which 'are not part of an aggregate
function.
Post your SQL so that a suggestion of how to change it can be determined.

 

[vanderghast]

Have you tried one of the four methods exposed at
http://www.mvps.org/access/queries/qry0020.htm ?


Vanderghast, Access MVP