How to delete the corresponding records

[UongSaki]

How do I create a query to delete, if there's a corresponding entry, i.e. credit/debit, row highlighted? Here a small sample of my table:

Cost Center Partner Cost Element Value COCurr Document header text
53005 71110 640290 ($5,057,647.97) NTDA1019980701Op Planning CC to Op
[COLOR=red yellow]53005 71110 640290 $5,057,647.97 NTDA1019980701Op Planning CC to Op[/color]
81000 71110 641240 ($3,530,589.59) NTDA6019980701N-GEN-ADM to General
81000 71110 641240 $3,513,062.15 NTDA6019980701N-GEN-ADM to General
53005 71110 640290 ($3,378,096.73) NTDA1019980701Op Planning CC to Op
[COLOR=red yellow]53005 71110 640290 $3,378,096.73 NTDA1019980701Op Planning CC to Op[/color]
81000 71110 641240 ($2,948,764.56) NTDA6019980701N-GEN-ADM to General
81000 71110 641240 $2,933,267.86 NTDA6019980701N-GEN-ADM to General
[COLOR=red yellow]53020 72210 640210 $2,669,229.84 NTDA2619980701N-53020 to PT DR Veh[/color]
53020 72210 640210 ($2,669,229.84) NTDA2619980701N-53020 to PT DR Veh
[COLOR=red yellow]53020 72210 640210 $2,602,452.53 NTDA2619980701N-53020 to PT DR Veh[/color]
53020 72210 640210 ($2,602,452.53) NTDA2619980701N-53020 to PT DR Veh
52600 71120 640160 ($2,571,642.40) NTDA3119980701C.A.R.S. to DO Veh M
[COLOR=red yellow]52600 71120 640160 $2,571,642.40 NTDA3119980701C.A.R.S. to DO Veh M[/color]
[COLOR=red yellow]53020 72210 640210 $2,498,672.11 NTDA2619980701N-53020 to PT DR Veh[/color]
53020 72210 640210 ($2,498,672.11) NTDA2619980701N-53020 to PT DR Veh
52100 71120 640260 ($2,488,464.25) NTDA2219980701N-MB-VEHM to DO MB V
[COLOR=red yellow]52100 71120 640260 $2,488,464.25 NTDA2219980701N-MB-VEHM to DO MB V[/color]
53020 72210 640210 ($2,424,788.15) NTDA2619980701N-53020 to PT DR Veh
[COLOR=red yellow]53020 72210 640210 $2,424,788.15 NTDA2619980701N-53020 to PT DR Veh[/color]
[COLOR=red yellow]53020 72210 640210 $2,416,852.97 NTDA2619980701N-53020 to PT DR Veh[/color]
53020 72210 640210 ($2,416,852.97) NTDA2619980701N-53020 to PT DR Veh
81000 71120 641240 $2,379,594.67 NTDA6019980701N-GEN-ADM to General
81000 71120 641240 ($2,366,005.00) NTDA6019980701N-GEN-ADM to General

Thank you,

Saki

 

[jksmi]

Hi,

looks like you need to delete positive Values only - where the sum of value = 0 for transactions grouped by Cost Center, Partner, Element & Doc header text. i.e. if the sum of values for
52600 | 71120 | 640160 | NTDA3119980701...etc
= 0 then delete any records for the corresponding group with positive values.

Is that right? Or is it just where positive values match negative exactly? I say this due to the last 2 records

HTH, Jamie
FAQ219-2884

 

[UongSaki]

Hi Jamie,

You are correct, I need to delete just those records where positive values match negative exactly. There could also be multiple entries with a credit/debit amounts, not just one +/-, and in that case I need only one record.
Thanks,

Saki

 

[jksmi]

Hi,

to delete the records that you have highlighted try the query below, it will need adapting according to your actual field & table names

DELETE TRANSACTIONS.*
FROM TRANSACTIONS INNER JOIN
(SELECT t1.COST_CENTRE, t1.PARTNER, t1.COST_ELEMENT, t1.VALUE_COCurr, t1.DOC_HDR_TEXT, CCur(Abs([t1].[VALUE_COCurr])) AS ABS_VALUE
FROM TRANSACTIONS AS t1
WHERE t1.VALUE_COCurr < 0
) AS d1
ON (TRANSACTIONS.DOC_HDR_TEXT = d1.DOC_HDR_TEXT) AND (TRANSACTIONS.VALUE_COCurr = d1.ABS_VALUE) AND (TRANSACTIONS.COST_ELEMENT = d1.COST_ELEMENT) AND (TRANSACTIONS.PARTNER = d1.PARTNER) AND (TRANSACTIONS.COST_CENTRE = d1.COST_CENTRE);

There could also be multiple entries with a credit/debit amounts, not just one +/-, and in that case I need only one record.

could you tell me a little more about what you want to happen here?

HTH, Jamie
FAQ219-2884