This should be easy, shouldn't it? Not Min query

[Mysterydave]

Hi,

I have a table which will have records appended to it weekly. In my append query I have a field to identify when it was appended using Now(). Now what I want to do is delete the identical records that were appended later so each record should only appear when they were appended for the first time.

So I basically am trying to select all the records that are not MIN on the appended date so I can then turn it into a delete query. I hope this is clear? How would I do this?

Your help is much appreciated.

[FishVal]

Make an aggregating query returning "min" records for each group.
Use "... WHERE ... Not In ..." syntax in your delete query.

Regards,
Fish.

[Mysterydave]

I am fumbling around SQL and it's not doing what it's told. If I gave a mini example of the situation, maybe you could help with the SQL.

Table appeded records go into = "DMH"
Field identifying when record each record was appended = "Updated Date"
Other fields include = "Name", "ADM_AOS_CODE", "Email"

Query selecting earliest Appended records, the records I want to keep = "DMEU"

[FishVal]

What field defines record groups from which earliest records are to be returned?

[Mysterydave]

That is the "Updated Date" field. I run the MIN function on this with all other fields in my table in the query. Did I understand you correctly?

[FishVal]

Does it mean there is only one record to keep?

[Mysterydave]

No, because the "Name" can be different etc.

[FishVal]

Ok. [Name] defines group from which earliest record is kept.
Is any from the rest fields a part of group definition?

[Mysterydave]

Sorry. To be specific: [ADM_ID] will be the field that will define the group.

[FishVal]

Something like the following

Query: qryRecordsToKeep

SELECT [ADM_ID], Min([Updated Date]) AS dteEarliestDate FROM [DMH] GROUP BY [ADM_ID];
 

DELETE * FROM [DMH]
WHERE [ADM_ID] Not In (SELECT dteEarliestDate FROM qryRecordsToKeep
);
 

Regards,
Fish

[Mysterydave]

Excellent. Thankyou very much!

[FishVal]

You are welcome.