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

Selasa, 27 Januari 2015

Re: [MS_AccessPros] How to separate duplicates.

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