Select max field with duplicate row entries

J

Jon Suen

Hi,

I'm just wondering if anyone was able to advise conceptually what I
could do in the following report:

I currently have a report of documents including their number and
'version' (if more than one exists, else this will be 0).
What I was after was a way to show only the latest (i.e. max() )
version for a corresponding row...

E.g. currently
Filename Version
TESTDOC 0
TESTDOC 1
TESTDOC 2
anotherFile 0
filenumberthree 0

to then:

Filename Version
TESTDOC 2
anotherFile 0
filenumberthree 0

Any ideas?

Thanks,
Jon
 
J

John Spencer

Query One:
SELECT FileName, Max(Version) as LatestVersion
FROM YourTable
GROUP BY FileName

Then use that query along with your table. Join on FileName, Version
(LatestVersion) to get all the details

SELECT YourTable.*
FROM YourTable INNER JOIN QueryOne
ON YourTable.FileName = QueryOne.FileName
AND YourTable.Version =QueryOne.LatestVersion



John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
 
J

Jon Suen

Thanks John, I will give this a go

Cheers,
Jon

Query One:SELECTFileName,Max(Version) as LatestVersion
FROM YourTable
GROUP BY FileName

Then use that query along with your table. Join on FileName, Version
(LatestVersion) to get all the details

SELECTYourTable.*
FROM YourTable INNER JOIN QueryOne
ON YourTable.FileName = QueryOne.FileName
AND YourTable.Version =QueryOne.LatestVersion

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top