Re: select from a table the highest revision number

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:

johnk@xxxxxxxxx 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.

.