asked on

Looking for a way to delete two records from a table whos sum = zero

cn_pk cn_contractorid      cn_slip      cn_paycycle        cn_total
1 ES-01        420908      Saturday, July 20, 2018      $480.07
2 ES-01        420908      Saturday, July 20, 2018      -$480.07
3 DH-02        420917      Saturday, July 21, 2018      -$436.89
4 DH-02        420917      Saturday, July 21, 2018      -$320.85
5 DH-02        420917      Saturday, July 21, 2018      $436.89
6 DH-02        420917      Saturday, July 21, 2018      $436.89
7 TR-03        420919      Saturday, July 23, 2018      -$463.40
8 TR-03        420919      Saturday, July 23, 2018      -$340.32
9 TR-03        420919      Saturday, July 23, 2018      $463.40
10 TR-03        420919      Saturday, July 23, 2018      $463.40

Hello,

I would like to write a delete records SQL that will delete any two records that who's combined sum of cn_total = zero where cn_contractorid, cn_slip, and cn_paycycle are the same?

In this example records 1, 2, 3, 5, 7 and 9 should delete.

bfuchs

would suggest something like

delete * from YourTable where dsum("cn_total","YourTable","cn_contractorid = '" & [cn_contractorid] & "') = 0

Open in new window

ASKER CERTIFIED SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

aikimark

@Gustav

Do you think you would you get a sufficiently reduced number of rows to improve performance if your initial Group By query looked for keys Having Count(ABS(cn_total)) >1?

Hard to tell with this limited (posted) sample size. It is a gut instinct on my part.

Dale Fye

I would not use bfuchs' method because that only has a criteria based on the contractorID and you explicitly indicated that the match had to occur across ContractorID, PayCycle, and Slip.

Well, to find the duplicates I would use:

SELECT T1.cn_pk, Min(t2.cn_PK) as Match
FROM yourTable as T1
INNER JOIN yourTable as T2
on T1.cn_contractorid = T2.cn_contractorid
AND T1.cn_slip = T2.cn_slip
AND T1.cn_paycycle = T2.cn_paycycle
AND T1.cn_total = - T2.cn_Total
AND t1.cn_pk < t2.cn_pk
GROUP BY T1.cn_pk

Open in new window

This would match each contractorID, paycycle, and slip against every other record in the table where all three of those columns match, and where the Total fields are opposites (+/-) of each other. The last criteria in the criteria string assumes that the cn_pk column is an autonumber, which generally means it is increasing, and you only want to compare T1 values to T2 values which were committed after each T1 transaction was committed.

I would then push this into a recordset and loop through the recordset deleting both the cn_pk and Match values from the main table.

Todd MacPherson

ASKER

@ Martyn Spencer cn_contractorid and/or the cn_slip must be the same on the records that sum to zero. The first instance of each is fine.

Todd MacPherson

ASKER

Not at my work station. Will test all solutions when I get there.

Thank you for your help. Stay tuned.

Todd MacPherson

ASKER

I went with Gustav Brock on this one. It works great.

Thank you.