Sorting deleting question

L

LOU

Good Day,

I have 3653 rows of data which have the following information by column;

ItemNumber Quantity Date
PRN223 56 02012003
PRN223 44 01042004
PRN223 41 06052003
PRN223 36 11272006
PRN223 49 08221999

What I need to do is first find the last time there was activity on the item
number, the example above would be the 11272006 date as the last activity, I
then want to purge all the other rows except the one record. I have 562
different item numbers total.

Thank you
Lou
 
S

Steve

You should have a primary key for each row also. Create a query based on
your table and include the following fields:
Primary Key
ItemNumber
Date
With your query in design view, click on the Sigma Button (looks like a
capital E) in the menu at the top of the screen. Now under the Date field,
change GroupBy to Max. The query will return the last activity for each
ItemNumber.

Open the database window to Queries and click on new. Create an unmatched
query basd on your table. Follow the directions. Only include the primary
key. When it asks for the other table or query, select the first query. When
it asks which field to compare, select the primary key. This query will
return all the activities except the last activity for each ItemNumber.

Finally convert the unmatched query to a delete query. With the query in
design view, click on the Type Of Query button in the menu at the top of the
screen and select Delete. This query will now delete all the activities
except the last activity for each ItemNumber.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
[email protected]
 
M

Michel Walsh

DELETE *
FROM tableName
WHERE date <> DMAX( "date", "tableName", "itemNumber=" & itemNumber )


(assuming itemNumber is an integer).

Hoping it may help,
Vanderghast, Access MVP
 
L

LOU

Thank you everyone, unfortunatelly the Item number is ALPHA NUMERIC so It
can't be an integer. The first try didn't work either for the max, it still
brings in all the records. Any ideas?

Thank you.
Lou
 
M

Michel Walsh

DELETE *
FROM tableName
WHERE date <> DMax("date", "tableName", "itemNumber=""" & itemNumber &
""" )



(the last two sequences of " are of 3 and 4 double quotes, that is,
inside the DMax( ) function. )

That assumes you don't have " in the itemNumber values !

That can be slow.

Make your experimentations on fake data or while having a good recent
backup.



Vanderghast, Access MVP
 
M

Marshall Barton

Just add quotes around the item number:

WHERE [date] <> DMAX( "[date]", "tableName", "itemNumber='"
& itemNumber & "' ")

If your dates are really an integer and not a date/time
value, then you will have to convert them to find the latest
one.
 
Top