Table Display

28 Nov 2007 10:28 PM

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?

28 Nov 2007 10:37 PM

John Spencer

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
'====================================================

mel_strom1***@hotmail.com wrote:
Show quote

> 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?

29 Nov 2007 3:09 PM

Ron2006

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

29 Nov 2007 4:46 PM

John Spencer

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
..

Show quote

"Ron2006" <ronne***@hotmail.com> wrote in message
news:ff42f1b3-5952-431b-aaac-e4c4307d0d63@e4g2000hsg.googlegroups.com...
> 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

29 Nov 2007 8:10 PM

Ron2006

Show quote

On Nov 29, 9:46 am, "John Spencer" <spen***@chpdm.edu> wrote:
> 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
> .
>
> "Ron2006" <ronne***@hotmail.com> wrote in message
>
> news:ff42f1b3-5952-431b-aaac-e4c4307d0d63@e4g2000hsg.googlegroups.com...
>
>
>
> > 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- Hide quoted text -
>
> - Show quoted text -

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