This should be easy, shouldn't it? Not Min query | Newbie | | Join Date: Dec 2008
Posts: 27
| | |
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.
|  | Expert | | Join Date: Jun 2007 Location: Israel
Posts: 2,584
| | | re: This should be easy, shouldn't it? Not Min query
Make an aggregating query returning "min" records for each group.
Use "... WHERE ... Not In ..." syntax in your delete query.
Regards,
Fish.
| | Newbie | | Join Date: Dec 2008
Posts: 27
| | | re: This should be easy, shouldn't it? Not Min query
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"
| | Expert | | Join Date: Jun 2007 Location: Israel
Posts: 2,584
| | | re: This should be easy, shouldn't it? Not Min query
What field defines record groups from which earliest records are to be returned?
| | Newbie | | Join Date: Dec 2008
Posts: 27
| | | re: This should be easy, shouldn't it? Not Min query
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?
| | Expert | | Join Date: Jun 2007 Location: Israel
Posts: 2,584
| | | re: This should be easy, shouldn't it? Not Min query
Does it mean there is only one record to keep?
| | Newbie | | Join Date: Dec 2008
Posts: 27
| | | re: This should be easy, shouldn't it? Not Min query
No, because the "Name" can be different etc.
| | Expert | | Join Date: Jun 2007 Location: Israel
Posts: 2,584
| | | re: This should be easy, shouldn't it? Not Min query
Ok. [Name] defines group from which earliest record is kept.
Is any from the rest fields a part of group definition?
| | Newbie | | Join Date: Dec 2008
Posts: 27
| | | re: This should be easy, shouldn't it? Not Min query
Sorry. To be specific: [ADM_ID] will be the field that will define the group.
| | Expert | | Join Date: Jun 2007 Location: Israel
Posts: 2,584
| | | re: This should be easy, shouldn't it? Not Min query
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
| | Newbie | | Join Date: Dec 2008
Posts: 27
| | | re: This should be easy, shouldn't it? Not Min query
Excellent. Thankyou very much!
| | Expert | | Join Date: Jun 2007 Location: Israel
Posts: 2,584
| | | re: This should be easy, shouldn't it? Not Min query
You are welcome.
|  |
Similar Microsoft Access / VBA bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,471 network members.
|
