ACCESS FEED: Re: [MS_AccessPros] How to separate duplicates.

Selasa, 27 Januari 2015

Re: [MS_AccessPros] How to separate duplicates.

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:

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