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: Robin Chapple <robinski@westnet.com.au>
Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (6) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar