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

Senin, 26 Januari 2015

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