Delete Duplicate Records?

[aellistechsupport]

There are duplicate records in the AdancedDues table.
This is how they are identified:

SELECT AdvancedDues.MemberId, AdvancedDues.JobclassId, AdvancedDues.EffectiveDateMbrPosition, AdvancedDues.dba_local_bu_jobclass_effectivedate, AdvancedDues.bu_jobclass_seq, AdvancedDues.dba_local_bu_rate_member_type, AdvancedDues.dba_local_bu_rate_effectivedate, AdvancedDues.bu_rate_seq, AdvancedDues.monthly_factor, AdvancedDues.factor, AdvancedDues.BargainingUnitId, AdvancedDues.type, AdvancedDues.dba_local_member_status_effectivedate, AdvancedDues.dba_local_member_status_member_typeFROM AdvancedDues
WHERE (((AdvancedDues.MemberId) In (SELECT [MemberId] FROM [AdvancedDues] As Tmp GROUP BY [MemberId],[JobclassId],[EffectiveDateMbrPosition],[dba_local_bu_jobclass_effectivedate],[bu_jobclass_seq],[dba_local_bu_rate_member_type],[dba_local_bu_rate_effectivedate],[bu_rate_seq],[monthly_factor],[factor] HAVING Count(*)>1  And [JobclassId] = [AdvancedDues].[JobclassId] And [EffectiveDateMbrPosition] = [AdvancedDues].[EffectiveDateMbrPosition] And [dba_local_bu_jobclass_effectivedate] = [AdvancedDues].[dba_local_bu_jobclass_effectivedate] And [bu_jobclass_seq] = [AdvancedDues].[bu_jobclass_seq] And [dba_local_bu_rate_member_type] = [AdvancedDues].[dba_local_bu_rate_member_type] And [dba_local_bu_rate_effectivedate] = [AdvancedDues].[dba_local_bu_rate_effectivedate] And [bu_rate_seq] = [AdvancedDues].[bu_rate_seq] And [monthly_factor] = [AdvancedDues].[monthly_factor] And [factor] = [AdvancedDues].[factor])))
ORDER BY AdvancedDues.MemberId, AdvancedDues.JobclassId, AdvancedDues.EffectiveDateMbrPosition, AdvancedDues.dba_local_bu_jobclass_effectivedate, AdvancedDues.bu_jobclass_seq, AdvancedDues.dba_local_bu_rate_member_type, AdvancedDues.dba_local_bu_rate_effectivedate, AdvancedDues.bu_rate_seq, AdvancedDues.monthly_factor, AdvancedDues.factor;

How do I delete the duplicate record?

MemberId	JobclassId	EffectiveDateMbrPosition	dba_local_bu_jobclass_effectivedate	bu_jobclass_seq	dba_local_bu_rate_member_type	dba_local_bu_rate_effectivedate	bu_rate_seq	monthly_factor	factor	BargainingUnitId	type	dba_local_member_status_effectivedate	dba_local_member_status_member_type
1529349	06DCLK	11/1/1996	6/2/1992	1989	83	6/1/1992	2025	35.36	8.16	101	66	11/1/1996	83
1529349	06DCLK	11/1/1996	6/2/1992	1989	83	6/1/1992	2025	35.36	8.16	101	66	11/1/1996	83
1529349	06DCLK	11/1/1996	6/2/1992	1989	83	1/1/1994	3789	39.69	9.16	101	66	11/1/1996	83
1529349	06DCLK	11/1/1996	6/2/1992	1989	83	1/1/1994	3789	39.69	9.16	101	66	11/1/1996	83
1529349	06DCLK	11/1/1996	6/2/1992	1989	83	6/1/1994	4466	40	9.23	101	66	11/1/1996	83
1529349	06DCLK	11/1/1996	6/2/1992	1989	83	6/1/1994	4466	40	9.23	101	66	11/1/1996	83
1529349	06DCLK	11/1/1996	6/2/1992	1989	83	3/1/1997	6438	41	9.46	101	66	11/1/1996	83
1529349	06DCLK	11/1/1996	6/2/1992	1989	83	3/1/1997	6438	41	9.46	101	66	11/1/1996	83
1529349	06DCLK	11/1/1996	6/2/1992	1989	83	9/1/1998	7845	42.12	9.72	101	66	11/1/1996	83
1529349	06DCLK	11/1/1996	6/2/1992	1989	83	9/1/1998	7845	42.12	9.72	101	66	11/1/1996	83
1529349	06DCLK	11/1/1996	6/2/1992	1989	83	3/1/2001	11028	43.77	10.1	101	66	11/1/1996	83
1529349	06DCLK	11/1/1996	6/2/1992	1989	83	3/1/2001	11028	43.77	10.1	101	66	11/1/1996	83
1529349	06DCLK	11/1/1996	6/2/1992	1989	83	6/1/2002	20769	44.27	10.22	101	66	11/1/1996	83
1529349	06DCLK	11/1/1996	6/2/1992	1989	83	6/1/2002	20769	44.27	10.22	101	66	11/1/1996	83
1529349	06DCLK	11/1/1996	6/2/1992	1989	83	1/1/2005	24509	45.27	10.45	101	66	11/1/1996	83
1529349	06DCLK	11/1/1996	6/2/1992	1989	83	1/1/2005	24509	45.27	10.45	101	66	11/1/1996	83
1529349	06DCLK	11/1/1996	6/2/1992	1989	83	1/1/2006	26262	45.77	10.57	101	66	11/1/1996	83
1529349	06DCLK	11/1/1996	6/2/1992	1989	83	1/1/2006	26262	45.77	10.57	101	66	11/1/1996	83
1529349	06DCLK	11/1/1996	6/2/1992	1989	83	12/1/2006	29125	46.77	10.8	101	66	11/1/1996	83
1529349	06DCLK	11/1/1996	6/2/1992	1989	83	12/1/2006	29125	46.77	10.8	101	66	11/1/1996	83
1529349	06DCLK	11/1/1996	6/2/1992	1989	83	1/1/2009	33926	47.77	11.04	101	66	11/1/1996	83
1529349	06DCLK	11/1/1996	6/2/1992	1989	83	1/1/2009	33926	47.77	11.04	101	66	11/1/1996	83

[aytee111]

See Delete Duplicates: http://allenbrowne.com/subquery-01.html

[aellistechsupport]

Unfortunately there is no primary key value id

"Delete duplicate records

This example uses a subquery to de-duplicate a table. "Duplicate" is defined as records that have the same values in Surname and FirstName. We keep the one that has the lowest primary key value (field ID.)

DELETE FROM Table1
WHERE ID <> (SELECT Min(ID) AS MinOfID FROM Table1 AS Dupe
WHERE (Dupe.Surname = Table1.Surname)
AND (Dupe.FirstName = Table1.FirstName)); "

[aytee111]

Make a copy of the table and add an autonumber field to provide it. Then copy the table back.

[orange]

Create a new table with the same format/design
add a composite unique index based on all the fields you feel make the duplicates

create a query to add your existing records to the new table

the composite unique index will not add the duplicates (that's its purpose)

Good luck

Why do you have duplicates?