finding and removing records that sum to zero based on two other columns

[dmz]

I have a dataset of about 70 columns but would like to remove any two rows that sum to zero (based on two columns) if the column "Units" are the same for the same "Bar code" thus to say "for the same 'Units' (for any two lines), check to see if their respective 'Bar Codes’ are the same and if so remove if their amounts sums to zero".
I have the following code which seems to be working but for relatively large data ( about 5k), it does not select all the data that meets the criteria:

SELECT *
FROM datatable
WHERE ((([datatable].[Unit] & [datatable].[Bar Code] & Abs([datatable].[Amount])) 
In (SELECT datatable.Unit&datatable.[Bar Code]&abs(datatable.Amount) 
FROM datatable 
GROUP BY datatable.Unit&datatable.[Bar Code]&abs(datatable.Amount) 
HAVING count(*)>= 2 and sum(datatable.Amount)=0)))
ORDER BY [datatable].Unit & [datatable].[Bar Code] & Abs([datatable].Amount);

I need a help identifying the bug in this code that makes it not pull all the data meeting the criteria.

See sample data below:
Please see the data below:

# Unit bar Code Amount

1. AAB Mac1 2.75
2. AAB Mac1 -2.75
3. AAB Mac1 1.24
4. AAC Mac3 35.00
5. AAC Mac3 -20.00
6. AAC Mac3 20.00
7. AAD Mac3 16.11
8. AAC Mac2 11.00
9. AAC Mac2 -11.00
10 AAC Mac3 12.05
11 AAF Mac3 -12.05
12 AAD Mac3 -16.11
13 AAC Mac4 35.00

I want to select records # 1, 2, 5, 6,7,8,9 and 12 since they are same units having the same bar code and their sums total zero.

dmz

[recyan]

At first glance, was wondering, what will happen, if you have another record :

14. AAB Mac1 2.75

Thanks

[June7]

You want to eliminate the positive/negative pairs? recyan poses interesting question. Could there be more than one positive or negative for any Unit/BarCode combination?

Criteria dependent on data in other records of same table can be tricky. Requires subquery or domain aggregate function.

Try:

SELECT Table1.* FROM Table1
WHERE (((DCount("*","Table1","[Unit]='" & [Unit] & "' AND [BarCode]='" & [BarCode] & "' AND Abs([Amount])=" & Abs([Amount])))>1));

[dmz]

You want to eliminate the positive/negative pairs? recyan poses interesting question. Could there be more than one positive or negative for any Unit/BarCode combination?

Criteria dependent on data in other records of same table can be tricky. Requires subquery or domain aggregate function.

Try:

SELECT Table1.* FROM Table1
WHERE (((DCount("*","Table1","[Unit]='" & [Unit] & "' AND [BarCode]='" & [BarCode] & "' AND Abs([Amount])=" & Abs([Amount])))>1));

Thanks June7,
This works for all Unit and BarCode combinations with abs of the amounts matching but need to work specifically for the positive/negative pairs for the Unit/BarCode combinations (where amounts sums to zero). I would therefore need one more step that would narrow the results to only those pairs summing to zero. I tried everything wiythout success. Note that am still learning!
recyan's scenario comes into play, so yes there could be more than one positive/negative pairs for any Unit/BarCode combinations

[June7]

So now the complication is to select one "AAB Mac1 2.75" or "AAB Mac1 -2.75" record and leave the twin(s) alone unless they both have twin(s), which would be another valid pair. Very, very tricky.

[recyan]

Assuming, that it is a one time operation,
check out if below (rather tedious) gives some ideas :
The table
dataTable

TheID
Unit
BarCode
Amount

2
AAB
Aac1
-2.75

15
AAB
Aac1
2.75

14
AAB
Aac1
2.75

3
AAB
Aac1
1.24

17
AAB
Aac1
-2.75

1
AAB
Aac1
2.75

10
AAC
Cac3
12.05

4
AAC
Cac3
35

5
AAC
Cac3
-20

6
AAC
Cac3
20

9
AAC
Bac2
-11

13
AAC
Dac4
35

16
AAC
Bac2
-11

8
AAC
Bac2
11

7
AAD
Cac3
16

12
AAD
Cac3
-16.11

11
AAF
Cac3
-12.05

1st Make-Table query


qryPositives

SELECT 
    dataTable.TheID, 
    dataTable.Unit, 
    dataTable.BarCode, 
    dataTable.Amount 
    INTO 
    tblqryPositives
FROM 
    dataTable
WHERE 
    (((dataTable.Amount)>0))
ORDER BY 
    dataTable.Unit, 
    dataTable.BarCode, 
    dataTable.Amount, 
    dataTable.TheID;

2nd Make-Table query


qryNegatives

SELECT 
    dataTable.TheID, 
    dataTable.Unit, 
    dataTable.BarCode, 
    dataTable.Amount 
INTO 
    tblqryNegatives
FROM 
    dataTable
WHERE 
    (((dataTable.Amount)<0))
ORDER BY 
    dataTable.Unit, 
    dataTable.BarCode, 
    dataTable.Amount, 
    dataTable.TheID;

After running the above 1st and 2nd Make-Table queries


3rd Make-Table query
qrytblqryPositivesRanking

SELECT 
    tblqryPositives.Unit, 
    tblqryPositives.BarCode, 
    tblqryPositives.Amount, 
    tblqryPositives.TheID, 
    Count(*) AS TheRank 
INTO 
    tblqrytblqryPositivesRanking
FROM 
    tblqryPositives 
    INNER JOIN 
    tblqryPositives AS tblqryPositives_1 
    ON 
    (tblqryPositives.Unit = tblqryPositives_1.Unit) 
    AND 
    (tblqryPositives.BarCode = tblqryPositives_1.BarCode) 
    AND 
    (tblqryPositives.Amount = tblqryPositives_1.Amount)
WHERE 
    (((tblqryPositives_1.TheID)<=[tblqryPositives].[TheID]))
GROUP BY 
    tblqryPositives.Unit, 
    tblqryPositives.BarCode, 
    tblqryPositives.Amount, 
    tblqryPositives.TheID
ORDER BY 
    tblqryPositives.Unit, 
    tblqryPositives.BarCode, 
    tblqryPositives.Amount, 
    tblqryPositives.TheID;

4th Make-Table query


qrytblqryNegativesRanking

SELECT 
    tblqryNegatives.Unit, 
    tblqryNegatives.BarCode, 
    tblqryNegatives.Amount, 
    tblqryNegatives.TheID, 
    Count(*) AS TheRank 
INTO 
    tblqrytblqryNegativesRanking
FROM 
    tblqryNegatives 
    INNER JOIN 
    tblqryNegatives AS tblqryNegatives_1 
    ON 
    (tblqryNegatives.Amount = tblqryNegatives_1.Amount) 
    AND 
    (tblqryNegatives.BarCode = tblqryNegatives_1.BarCode) 
    AND 
    (tblqryNegatives.Unit = tblqryNegatives_1.Unit)
WHERE 
    (((tblqryNegatives_1.TheID)<=[tblqryNegatives].[TheID]))
GROUP BY 
    tblqryNegatives.Unit, 
    tblqryNegatives.BarCode, 
    tblqryNegatives.Amount, 
    tblqryNegatives.TheID
ORDER BY 
    tblqryNegatives.Unit, 
    tblqryNegatives.BarCode, 
    tblqryNegatives.Amount, 
    tblqryNegatives.TheID;

After running the 3rd and 4th Make_table queries


the Final query to run


qryFinal

SELECT 
    tblqrytblqryPositivesRanking.Unit, 
    tblqrytblqryPositivesRanking.BarCode, 
    tblqrytblqryPositivesRanking.Amount, 
    tblqrytblqryPositivesRanking.TheID, 
    tblqrytblqryPositivesRanking.TheRank, 
    tblqrytblqryNegativesRanking.Unit, 
    tblqrytblqryNegativesRanking.BarCode, 
    tblqrytblqryNegativesRanking.Amount, 
    tblqrytblqryNegativesRanking.TheID, 
    tblqrytblqryNegativesRanking.TheRank
FROM 
    tblqrytblqryPositivesRanking 
    INNER JOIN 
    tblqrytblqryNegativesRanking 
    ON 
    (tblqrytblqryPositivesRanking.Unit=tblqrytblqryNegativesRanking.Unit) 
    AND 
    (tblqrytblqryPositivesRanking.BarCode=tblqrytblqryNegativesRanking.BarCode) 
    AND 
    (tblqrytblqryPositivesRanking.Amount=ABS(tblqrytblqryNegativesRanking.Amount)) 
    AND 
    (tblqrytblqryPositivesRanking.TheRank=tblqrytblqryNegativesRanking.TheRank);

tblqrytblqryPositivesRanking.Unit
tblqrytblqryPositivesRanking.BarCode
tblqrytblqryPositivesRanking.Amount
tblqrytblqryPositivesRanking.TheID
tblqrytblqryPositivesRanking.TheRank
tblqrytblqryNegativesRanking.Unit
tblqrytblqryNegativesRanking.BarCode
tblqrytblqryNegativesRanking.Amount
tblqrytblqryNegativesRanking.TheID
tblqrytblqryNegativesRanking.TheRank

AAB
Aac1
2.75
1
1
AAB
Aac1
-2.75
2
1

AAB
Aac1
2.75
14
2
AAB
Aac1
-2.75
17
2

AAC
Bac2
11
8
1
AAC
Bac2
-11
9
1

AAC
Cac3
20
6
1
AAC
Cac3
-20
5
1

Note : Be sure to take a look at my signature, specially the last part.

Thanks

[dmz]

The code worked and problem solved! I just had to add a query to pick TheID for the negative and the positive from qryFinal and created a union query to put them together in the same format as the dataset and done! Thank you guys, you are great people here!