Trying to delete records

[vba317]

I am trying to write a query that would delete duplicate records. I tried to use the find duplicate query but I have 13 columns that exceed the 10 field max from the find dup query.

I tried to set the unigue records to no but this didnt help either.

I have created a second table for these records to go into. Right now when I run this query I get no records returned. Which I believe I should have at least 800 dups.
The fields that I need to check for dups are: PostPeriod, PayCode, Provider, ServicePeriod,InvoiceCreationPeriod,CPT,Volume,ChargeAmt,PaymentAmt,AdjustmentAmt,DebitAmt,CreditAmt.

SELECT dbo_BWF_exp_File.PostPeriod, dbo_BWF_exp_File.PayCode, dbo_BWF_exp_File.Provider, dbo_BWF_exp_File.ServicePeriod, dbo_BWF_exp_File.InvoiceCreationPeriod, dbo_BWF_exp_File.CPT, dbo_BWF_exp_File.Volume, dbo_BWF_exp_File.ChargeAmt, dbo_BWF_exp_File.PaymentAmt, dbo_BWF_exp_File.UCI, dbo_BWF_exp_File.AdjustmentAmt, dbo_BWF_exp_File.DebitAmt, dbo_BWF_exp_File.CreditAmt
FROM dbo_BWF_exp_File
WHERE (((dbo_BWF_exp_File.PostPeriod) In (SELECT [PostPeriod] FROM [dbo_BWF_exp_File] As Tmp GROUP BY [PostPeriod],[PayCode],[Provider],[ServicePeriod],[InvoiceCreationPeriod],[CPT],[Volume],[ChargeAmt],[PaymentAmt] HAVING Count(*)>1  And [PayCode] = [dbo_BWF_exp_File].[PayCode] And [Provider] = [dbo_BWF_exp_File].[Provider] And [ServicePeriod] = [dbo_BWF_exp_File].[ServicePeriod] And [InvoiceCreationPeriod] = [dbo_BWF_exp_File].[InvoiceCreationPeriod] And [CPT] = [dbo_BWF_exp_File].[CPT] And [Volume] = [dbo_BWF_exp_File].[Volume] And [ChargeAmt] = [dbo_BWF_exp_File].[ChargeAmt] And [PaymentAmt] = [dbo_BWF_exp_File].[PaymentAmt])))
ORDER BY dbo_BWF_exp_File.PostPeriod, dbo_BWF_exp_File.PayCode, dbo_BWF_exp_File.Provider, dbo_BWF_exp_File.ServicePeriod, dbo_BWF_exp_File.InvoiceCreationPeriod, dbo_BWF_exp_File.CPT, dbo_BWF_exp_File.Volume, dbo_BWF_exp_File.ChargeAmt, dbo_BWF_exp_File.PaymentAmt;

 

[PHV]

What is true ?
but I have 13 columns
vs
The fields that I need to check for dups are: PostPeriod, PayCode, Provider, ServicePeriod,InvoiceCreationPeriod,CPT,Volume,ChargeAmt,PaymentAmt,AdjustmentAmt,DebitAmt,CreditAmt
vs
GROUP BY [PostPeriod],[PayCode],[Provider],[ServicePeriod],[InvoiceCreationPeriod],[CPT],[Volume],[ChargeAmt],[PaymentAmt]

Anyway, what is the PrimaryKey of dbo_BWF_exp_File ?

 

[vba317]

It does not have one, which is why I am running into this problem. The person who designed the SQL and databases didnt believe in them. He left last week and now I have to clean up his mess.

 

[vba317]

The extra columns are : AdjustmentAmount, DebitAmt, CreditAmt

 

[Andrzejek]

One way (long way) to do it would be:
1. Create a temp table with all your fields
2. Copy records you do want to keep (something like:
Insert Into TampTable (Select Distinct ... From MyOriginalTable...))
3. Delete all records from your original table
4. Either rename temptable or move all records from temptable back to your original (now empty) table

Have fun.

---- Andy