SQL delete query for duplicate records in Access

This is a follow up question to an earlier question.  I have an Access database with duplicate records.  The key is a composite of FUND, GROUP, ID.  How would I write a query to delete duplicate records in the table.
VBStudentAsked:
Who is Participating?
 
jamesguConnect With a Mentor Commented:
delete from <table>
from <table>
inner join ( select FUND, GROUP, ID from <table> group by FUND, GROUP, ID having count(*) > 1) b on
<table>.FUND = a.FUND
<table>.GROUP = GROUP
<table>.ID = a.ID

--missed a from clause
0
 
jazzIIIloveCommented:
Hi there;

DELETE FROM our_table WHERE rowid not in(SELECT MIN(rowid) FROM our_tabl eGROUP BY column1, column2, column3... ;

Here column1, column2, column3 constitute the identifying key for each record.

Best regards...
0
 
VBStudentAuthor Commented:
it doesn't appear as though rowid is recognized in Access.  Any other suggestions?
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
UniqueDataCommented:
don't award any points to me, just thought I would help out...

rowid is probably referring to a unique record id, usually an AutoNumber field.  I know you have the three fields that make up the key, but do you also have (or can you add) an AutoNumber?

Michael
0
 
jamesguCommented:
delete from <table>
inner join ( select FUND, GROUP, ID from <table> group by FUND, GROUP, ID having count(*) > 1) b on
<table>.FUND = a.FUND
<table>.GROUP = GROUP
<table>.ID = a.ID



0
 
jazzIIIloveCommented:
yes...rowId is autoNumbered field of yours...
0
 
VBStudentAuthor Commented:
Thank you for your help
0
 
sarcharanCommented:
I thank all of you for helping me. I found one more document to be quite interesting.

delete from t1 where tl.rowid >(select min(tl2.rowID) from t1 tl2 where tl.col1 = tl2.col1 and tl.col2 = tl2.col2)
====================
sp_rename 'dup_authors', 'temp_dup_authors'

select distinct *
into dup_authors
from temp_dup_authors

drop table temp_dup_authors
Deleting-Duplicate-Records.doc
0
All Courses

From novice to tech pro — start learning today.