Robin-
The first query puts tblClubPostHoldersID into a new table where the value is in the set of the the smallest values (SELECT MIN) in any groups of (GROUP BY) ClubPostID, MemberID, YearID, and ClubID that have more than one row (HAVING Count(*) > 1).
The second query simply deletes rows that match any tblClubPostHoldersID found in the new table.
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
(Paris, France)
On Jan 27, 2015, at 11:34 AM, Robin Chapple robinski@westnet.com.au [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
G'day John,
The SQL was just like Greek to me but I did understand what each step was designed to do. I had the database backed up in two separate places and proceeded with your handiwork. The SELECT query must have taken nearly ten minutes to work through the 25,000 records but it did make a table with 951 records.
The second query did delete the duplicates. That is magic to me!
I must now attempt to really understand that T2 table. You used that technique once before. I will look up my notes.
I am most grateful.
Regards,
Robin
At 27/01/2015 07:03 PM, you wrote:
The SQL was just like Greek to me but I did understand what each step was designed to do. I had the database backed up in two separate places and proceeded with your handiwork. The SELECT query must have taken nearly ten minutes to work through the 25,000 records but it did make a table with 951 records.
The second query did delete the duplicates. That is magic to me!
I must now attempt to really understand that T2 table. You used that technique once before. I will look up my notes.
I am most grateful.
Regards,
Robin
At 27/01/2015 07:03 PM, you wrote:
Robin-
Your queries might look like:
SELECT tblClubPostHoldersID INTO mtblDupClubRecs
FROM tClubPostHolders
WHERE tClubPostHolders.tblClubPostHoldersID IN
(SELECT MIN(tblClubPostHoldersID)
FROM tClubPostHolders As T2
GROUP BY ClubPostID, MemberID, YearID, ClubID
HAVING Count(*) > 1);
DELETE * FROM tClubPostHolders
WHERE tClubPostHolders.tblClubPostHoldersID IN
(SELECT tblClubPostHoldersID
FROM mtblDupClubRecs);
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
On Jan 27, 2015, at 4:36 AM, Stephen Conklin StephenMConklin@hotmail.com [MS_Access_Professionals] < MS_Access_Professionals@yahoogroups.com> wrote:
Robin:
Duane is saying, if I may, to choose the min or max on your primary key to determine which of the duplicates to delete.
Once you have done that, do a make table on the pk_id’s that you want to get rid of. Then, and this is my suggestion, do a DELETE * FROM tbl WHERE pk_id IN (SELECT pk_id FROM â€"the table you just made of the pk_id’s to delete-).
Forgive me for not following your field names, but I hope this helps.
Steve
From: MS_Access_Professionals@yahoogroups.com [ mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Monday, January 26, 2015 9:10 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] How to separate duplicates.
Thanks Duane,
I do not understand = " You would need to add the Min() or Max() (the record you want to get rid of) of the primary key."
I tried to include the existing primary key field but the query failed.
The primary field is "tblClubPostHoldersID" and my guess is the I need to add code to find the lowest number.
Please assist.
Many thanks,
Robin Chapple
At 27/01/2015 10:40 AM, you wrote:
Robin,
Any query with GROUP BY does not allow updates or deletes.
I would use the totals query as a source for a make table query. You would need to add the Min() or Max() (the record you want to get rid of) of the primary key. Set the Min or Max as a primary key in the made table. Then join the made table with tClubPostHolders in a delete query to select the records from tClubPostHolders that you want to delete.
Always work with a copy when doing mass action queries.
Duane Hookom MVP
MS Access
To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Tue, 27 Jan 2015 10:07:36 +1100
Subject: Re: [MS_AccessPros] How to separate duplicates.
I have another table with 950 duplicates in a total 25,400 records. I have attempted to delete then using the suggestion below as the example.
The news SQL is:
SELECT ClubPostID, MemberID, YearID, ClubID
FROM tClubPostHolders
GROUP BY ClubPostID, MemberID, YearID, ClubID
HAVING Count(*) > 1;
The 'View' provides 950 records. If I attempt a 'Delete' query I get the error message: "Cannot have an aggregate function in a 'where' clause".
How do I delete these records?
Many thanks.
Robin Chapple
At 8/01/2015 12:47 AM, you wrote:
Robin-
One way to do it:
SELECT FirstName, LastName
FROM MyTable
GROUP BY FirstName, LastName
HAVING Count(*) > 1;
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
On Jan 7, 2015, at 11:04 AM, Robin Chapple robinski@westnet.com.au [MS_Access_Professionals] < MS_Access_Professionals@yahoogroups.com> wrote:
I have a recordset with two fields, [FirstName] and [LastName].
How do I extract records with those fields duplicated. I can make a
single field [FullName] if that would help.
Many thanks,
Robin Chapple
------------------------------------
Posted by: Robin Chapple <robinski@westnet.com.au >
------------------------------------
------------------------------------
Yahoo Groups Links
__._,_.___
Posted by: John Viescas <johnv@msn.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (10) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar