Delete Max record from a Table

[ECEK]

I have table1 that I append data to. The unique_ID is the rpt_date.
On form1 (that shows the table1 data) I want to delete the Max rpt_date from the table1.

I have tried to create a query involving MaxOfrpt_date linked to the table1 by the rpt_date and then create a delete query but it doesn't work !!
Any help ?

 

[James Deckert]

what about something like
DELETE

.[Date Entered]
FROM

WHERE (((

.[Date Entered]) In (SELECT Max(

.[Date Entered]) AS [MaxOfDate Entered]
FROM

));

 

[chrisdd]

I'm using this SQL to delete duplicate records

DELETE
FROM [_TRSMaterials] AS a
WHERE a.wasteid NOT IN
(
SELECT Max(b.WasteID)
FROM [_TRSMaterials] As b
WHERE a.Material=b.Material

There are duplicate entries in the Material field

However, every time I run the query I get The search key was not found in any record?

Any help much appreciated as I've tried loads of ways to do this and get the same error every time#

 

[Gasman]

Seriously 9 year old thread?
Start your own.
However, how do you expect to NOT find the wasteID when you are using the same table?
I would have probably used count() > 1 for material with the Max.
To debug, run each query on it's own and see what you get. That should help your logic.

 

[MajP]

The search key was not found in any record?

That is usually not a good sign. Often associated with corruption. At a minimum close the db. Then Compact and repair it. Is there any other strange things happening?
I would search this forum for "search key not found errors" and see there are a lot of different things that cause this. One thing I saw was removing the primary key and indexes and saving the table. Then adding the PK and indices back in.

 

[Gasman]

Ho

That is usually not a good sign. Often associated with corruption. At a minimum close the db. Then Compact and repair it. Is there any other strange things happening?
I would search this forum for "search key not found errors" and see there are a lot of different things that cause this. One thing I saw was removing the primary key and indexes and saving the table. Then adding the PK and indices back in.

When is a key in a not going to be in b when it is the same table?

 

[MajP]

When is a key in a not going to be in b when it is the same table?

When the table is corrupted.

 

[plog]

Why are you doing this? What are you hoping to accomplish? How are duplicates getting in? The whole premise is very suspect, this isn't normally how databases are to work.



Perhaps you can give us a big picture of what this database is for and what you are ultimately trying to accomplish with this data cleanup piece you are trying to implement.

 

[arnelgp]

you can simply:

DELETE * FROM [_TRSMaterials] WHERE wasteid <> DMax("wasteid", "[_TRSMaterials]","Material = '" & [Material] & "'")