Sorting deleting question

[Guest]

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

 

[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
(e-mail address removed)

 

[StopThisAdvertising]

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

--
==>Let's ask it the 'nice' way...
Would you please, please go away Steve ??

This is to inform 'newbees' here about PCD' Steve:
http://home.tiscali.nl/arracom/whoissteve.html
Until now 3700+ pageloads, 2375+ first-time visitors
(these figures are real and rapidly increasing)
http://home.tiscali.nl/arracom/SteveStats.html

Why is this ???
Because Steve is the ONLY person here who continues to advertise in the groups.

It is not relevant whether he advertised in *this* particular post or not...
==> We want him to know that these groups are *not* his private hunting grounds!

For those who don't like too see all these messages:
==> Simply killfile 'StopThisAdvertising'.
Newbees will still see this warning-message.

ArnoR

 

[Michel Walsh]

You can delete on a GROUP BY query?

Vanderghast, Access MVP

 

[Michel Walsh]

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


(assuming itemNumber is an integer).

Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

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

 

[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

 

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