select from a table the highest revision number

[johnk]

I have a table of items, with revision numbers. I need to extract the
items with highest revision number. The items may be listed several
times and I don't know what the highest revision number for each item
is. How do I do this?

[salad]

jo***@graham.ca wrote:

I have a table of items, with revision numbers. I need to extract the
items with highest revision number. The items may be listed several
times and I don't know what the highest revision number for each item
is. How do I do this?

Unknown. Do you want to extract the highest revision number for all
items? Do you want to extract the the highest revision number for a
particular item?

It appears you want to return 1-many rows. Why not use a totals query?

[asc4john]

I would like to return all of the items with the highest revision
numbers.
ItemNum rev
dwg-123 01
dwg-123 02
dwg-234 01
dwg-345 01
dwg-345 02
dwg-345 03

I only want dwg 123 rev02, dwg-234 rev01, and dwg-345 rev03.
I don't know how many revisions per drawing or what the highest
revision might be.
salad wrote:

jo***@graham.ca wrote:

I have a table of items, with revision numbers. I need to extract the
items with highest revision number. The items may be listed several
times and I don't know what the highest revision number for each item
is. How do I do this?

Unknown. Do you want to extract the highest revision number for all
items? Do you want to extract the the highest revision number for a
particular item?

It appears you want to return 1-many rows. Why not use a totals query?

[salad]

asc4john wrote:

I would like to return all of the items with the highest revision
numbers.
ItemNum rev
dwg-123 01
dwg-123 02
dwg-234 01
dwg-345 01
dwg-345 02
dwg-345 03

I only want dwg 123 rev02, dwg-234 rev01, and dwg-345 rev03.
I don't know how many revisions per drawing or what the highest
revision might be.
salad wrote:

jo***@graham.ca wrote:

I have a table of items, with revision numbers. I need to extract the
items with highest revision number. The items may be listed several
times and I don't know what the highest revision number for each item
is. How do I do this?

Unknown. Do you want to extract the highest revision number for all
items? Do you want to extract the the highest revision number for a
particular item?

It appears you want to return 1-many rows. Why not use a totals query?

Dmax and others aggregate functions only return 1 record. That is why I
mentioned a Totals Query.

If you know the item #, you can get the highest revision using DMax.

If you want to return a series of items and their revision numbers then
Create a query/new and select the table
Drag the two fields, Item and Revision to the columns.
Click View/Totals from the menu bar
Put GroupBy under Item
Select Max under Revision.
Save query and run to view results.

You can use this query to select records in a combo/list box or you can
use if to find records in the recordset.

[Bob Quintal]

jo***@graham.ca wrote in
news:11**********************@g47g2000cwa.googlegr oups.com:

I have a table of items, with revision numbers. I need to
extract the items with highest revision number. The items may
be listed several times and I don't know what the highest
revision number for each item is. How do I do this?

This requires a subquery, to select the max() of revision from each
Item.

select item, revision from table where revision IN (SELECT max
(revision) from table where ITEM = [ITEM])

Or you can use the Dmax() function:
SELECT Item, revision FROM table WHERE
revision = dmax("revision","table","Item = '" & [item] & "'")

This may prove too slow, so you can instead make a permanent totals
query that groups by the item,and the max(revision) and do an inner
join back to the main table in a second query.

--
Bob Quintal

PA is y I've altered my email address.