By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,773 Members | 2,307 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,773 IT Pros & Developers. It's quick & easy.

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

P: 28
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.
Feb 27 '09 #1
Share this Question
Share on Google+
11 Replies


FishVal
Expert 2.5K+
P: 2,653
Make an aggregating query returning "min" records for each group.
Use "... WHERE ... Not In ..." syntax in your delete query.

Regards,
Fish.
Feb 27 '09 #2

P: 28
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"
Feb 27 '09 #3

FishVal
Expert 2.5K+
P: 2,653
What field defines record groups from which earliest records are to be returned?
Feb 27 '09 #4

P: 28
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?
Feb 27 '09 #5

FishVal
Expert 2.5K+
P: 2,653
Does it mean there is only one record to keep?
Feb 27 '09 #6

P: 28
No, because the "Name" can be different etc.
Feb 27 '09 #7

FishVal
Expert 2.5K+
P: 2,653
Ok. [Name] defines group from which earliest record is kept.
Is any from the rest fields a part of group definition?
Feb 27 '09 #8

P: 28
Sorry. To be specific: [ADM_ID] will be the field that will define the group.
Feb 27 '09 #9

FishVal
Expert 2.5K+
P: 2,653
Something like the following

Query: qryRecordsToKeep

Expand|Select|Wrap|Line Numbers
  1. SELECT [ADM_ID], Min([Updated Date]) AS dteEarliestDate FROM [DMH] GROUP BY [ADM_ID];
  2.  
Expand|Select|Wrap|Line Numbers
  1. DELETE * FROM [DMH]
  2. WHERE [ADM_ID] Not In (SELECT dteEarliestDate FROM qryRecordsToKeep
  3. );
  4.  
Regards,
Fish
Feb 27 '09 #10

P: 28
Excellent. Thankyou very much!
Feb 27 '09 #11

FishVal
Expert 2.5K+
P: 2,653
You are welcome.
Feb 27 '09 #12

Post your reply

Sign in to post your reply or Sign up for a free account.