removing duplicates using a criteria

[william]

I have a large database of inventory items. Each time we sell the item the
database adds entry into table that shows the following:
item
qty
date
ordernumb
I need to make a table that shows an items last sold date.
For example
item qty date
1 1 1/2/2009
1 2 1/3/2009
2 1 1/2/2009
2 3 1/4/2009

I want to remove all of the duplicates and only return the following

1 2 1/3/2009
2 3 1/4/2009

Please help- this way my table will only show the newest items sold.

 

[william]

Marshall,

Thanks I will try your suggestion. That data was not real. My data comes
from Great Plains Microsoft Business solution table.

 

[William]

Marshall,
It did not work - syntax errors. my fault.

Heres my true data.
Table name: invsoldbydate
field names:
soptype,sopnumbe,itemnmbr,docdate
itemnmbr = item
docdat = date

Please help.

 

[Marshall Barton]

I can't tell what you might have done wrong in the query
unless I can see the query's SQL view. Use Copy/Paste so
don't waste time on typos.

Be sure to explain which fields are really needed in the
query's result.