Table Display

  • Thread starter Thread starter mel_strom1228
  • Start date Start date
M

mel_strom1228

I have two columns: document number and document version. I want the
table to display only the most recent version (which would be the
highest number) of a document. How can I do that?
 
You would need to use a query.

SELECT [Document Number], Max([Document Version]) as latest
FROM YourTableName

In the query grid
-- Add both fields
-- Select View: Totals from the menu
-- Change Group By to Max under the version field.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
here is an alternative that I have sometimes used.

SELECT TOP 1 [pay table].[pay rate]
FROM [pay table]
ORDER BY [pay table].[pay rate] DESC;

Using the document version it would be something like:

SELECT TOP 1 [Document Number], [Document Version]
FROM YourTableName
ORDER BY [Document Version] DESC;

As long as document version is numeric, there is no problem. You may
have to watch out if it is alphabetic.

Ron
 
Your solution would display all documents that had the highest version
number in the table. Not the Highest version number of each document.

Doc : Vers
A : 1
A : 2
B : 1
B : 2
B : 3
C : 1
C : 2
C : 3

Your solution would return
B : 3
C : 3

What I thought the user wanted would be
A : 2
B : 3
C: 3

You could modify your solution to get the above result.

SELECT [Document Number], [Document Version]
FROM YourTableName
WHERE [Document Version] in (
SELECT TOP 1 [Document Version]
FROM YourTableName as Temp
WHERE Temp.[Document Number] = YourTableName.[Document Number]
ORDER BY [Document Version] DESC)
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Your solution would display all documents that had the highest version
number in the table. Not the Highest version number of each document.

Doc : Vers
A : 1
A : 2
B : 1
B : 2
B : 3
C : 1
C : 2
C : 3

Your solution would return
B : 3
C : 3

What I thought the user wanted would be
A : 2
B : 3
C: 3

You could modify your solution to get the above result.

SELECT [Document Number], [Document Version]
FROM YourTableName
WHERE [Document Version] in (
SELECT TOP 1 [Document Version]
FROM YourTableName as Temp
WHERE Temp.[Document Number] = YourTableName.[Document Number]
ORDER BY [Document Version] DESC)
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.




here is an alternative that I have sometimes used.
Click to expand...
SELECT TOP 1 [pay table].[pay rate]
FROM [pay table]
ORDER BY [pay table].[pay rate] DESC;
Click to expand...
Using the document version it would be something like:
Click to expand...
SELECT TOP 1 [Document Number], [Document Version]
FROM YourTableName
ORDER BY [Document Version] DESC;
Click to expand...
As long as document version is numeric, there is no problem. You may
have to watch out if it is alphabetic.
Click to expand...
Ron- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Interesting, I usually use it for the highest value and typically use
the dlookup to get it, so it accomplishes the same thing, But that is
because what I have used it for has unique numbers and I am trying to
assign the next successive value.

And also you bring out correctly that my solution did NOT account for
the highest version for that document. It would get all the highest
values for all documents not just the specific document.

Good catch.

Ron
 
Back
Top