Gustav Brock
gustav at cactus.dk
Wed Oct 15 09:37:16 CDT 2003
Hi Lonnie
> This is one for the math guys who code. I have a situation where I
> need to take a field in a group of records and see if any
> combination of the values in the field equal zero.
> Example:
> MyField
> 5
> -2
> 7
> -3
> 6
> This group of records would have a combination that equals zero (5,
> -2, -3). I hope someone has something.
Watch out - long post.
It can be done in Jet if you limit the number of rows to about six.
The actual number depends on your machine power and client patience.
1. Source table, tblAmount.
I've added one more row to obtain two solutions for a zero sum:
ID Amount
1 5,00
2 -2,00
3 7,00
4 -3,00
5 6,00
6 -1,00
2. Create a query which returns all rows included and excluded:
<SQL>
SELECT
ID,
True As Include,
Amount
FROM
tblAmount
UNION ALL
SELECT
ID,
False As Include,
0 As Amount
FROM
tblAmount;
</SQL>
Save this as qdyAmountInclude.
This will return twelve rows:
ID Include Amount
1 -1 5,00
1 0 0,00
2 -1 -2,00
2 0 0,00
3 -1 7,00
3 0 0,00
4 -1 -3,00
4 0 0,00
5 -1 6,00
5 0 0,00
6 -1 -1,00
6 0 0,00
3. Create a query which multiplies the above query six fold to obtain
all combinations of exluded and included rows:
<SQL>
SELECT
T1.ID, T1.Include, T1.Amount,
T2.ID, T2.Include, T2.Amount,
T3.ID, T3.Include, T3.Amount,
T4.ID, T4.Include, T4.Amount,
T5.ID, T5.Include, T5.Amount,
T6.ID, T6.Include, T6.Amount
FROM
qdyAmountInclude AS T1,
qdyAmountInclude AS T2,
qdyAmountInclude AS T3,
qdyAmountInclude AS T4,
qdyAmountInclude AS T5,
qdyAmountInclude AS T6;
</SQL>
Save it as qdyAmountCombine.
Run it if you like - but it will return about 3 mio. records ...
4. Create a query which allows to filter and group on only those
records interesting.
To do so, a bitmap is set up which uniquely identify a combination of
rows from tblAmount no matter the sequence of the selected rows.
By decoding the bitmap the included rows of tblAmount is shown.
<SQL>
SELECT DISTINCT
([T1]![Include]*2^([T1]![ID]-1))+
([T2]![Include]*2^([T2]![ID]-1))+
([T3]![Include]*2^([T3]![ID]-1))+
([T4]![Include]*2^([T4]![ID]-1))+
([T5]![Include]*2^([T5]![ID]-1))+
([T6]![Include]*2^([T6]![ID]-1)) AS Combination,
([Combination] Mod 2^1)\2^(1-1) AS ID1,
([Combination] Mod 2^2)\2^(2-1) AS ID2,
([Combination] Mod 2^3)\2^(3-1) AS ID3,
([Combination] Mod 2^4)\2^(4-1) AS ID4,
([Combination] Mod 2^5)\2^(5-1) AS ID5,
([Combination] Mod 2^6)\2^(6-1) AS ID6
FROM
qdyAmountCombine AS Tn
WHERE
((([T1]![Include]*2^([T1]![ID]-1))+
([T2]![Include]*2^([T2]![ID]-1))+
([T3]![Include]*2^([T3]![ID]-1))+
([T4]![Include]*2^([T4]![ID]-1))+
([T5]![Include]*2^([T5]![ID]-1))+
([T6]![Include]*2^([T6]![ID]-1))<>0)
AND (
(Tn.T1.ID)<>[T2]![ID] And
(Tn.T1.ID)<>[T3]![ID] And
(Tn.T1.ID)<>[T4]![ID] And
(Tn.T1.ID)<>[T5]![ID] And
(Tn.T1.ID)<>[T6]![ID])
AND (
(Tn.T2.ID)<>[T1]![ID] And
(Tn.T2.ID)<>[T3]![ID] And
(Tn.T2.ID)<>[T4]![ID] And
(Tn.T2.ID)<>[T5]![ID] And
(Tn.T2.ID)<>[T6]![ID])
AND (
(Tn.T3.ID)<>[T1]![ID] And
(Tn.T3.ID)<>[T2]![ID] And
(Tn.T3.ID)<>[T4]![ID] And
(Tn.T3.ID)<>[T5]![ID] And
(Tn.T3.ID)<>[T6]![ID])
AND (
(Tn.T4.ID)<>[T1]![ID] And
(Tn.T4.ID)<>[T2]![ID] And
(Tn.T4.ID)<>[T3]![ID] And
(Tn.T4.ID)<>[T5]![ID] And
(Tn.T4.ID)<>[T6]![ID])
AND (
(Tn.T5.ID)<>[T1]![ID] And
(Tn.T5.ID)<>[T2]![ID] And
(Tn.T5.ID)<>[T3]![ID] And
(Tn.T5.ID)<>[T4]![ID] And
(Tn.T5.ID)<>[T6]![ID])
AND (
(Tn.T6.ID)<>[T1]![ID] And
(Tn.T6.ID)<>[T2]![ID] And
(Tn.T6.ID)<>[T3]![ID] And
(Tn.T6.ID)<>[T4]![ID] And
(Tn.T6.ID)<>[T5]![ID])
AND (
[T1]![Amount]+
[T2]![Amount]+
[T3]![Amount]+
[T4]![Amount]+
[T5]![Amount]+
[T6]![Amount]=0));
</SQL>
Save this as qdyAmountZero.
Run it and note that it runs surprisingly fast.
It should return two rows:
Combination ID1 ID2 ID3 ID4 ID5 ID6
-58 0 -1 0 -1 -1 -1
-11 -1 -1 0 -1 0 0
where the number if ID, say ID4, identifies the ID of the row of
tblAmount.
5. Now, create a query to display the possible selection(s).
<SQL>
SELECT
Abs([Combination]) AS Selection,
tblAmount.ID,
tblAmount.Amount
FROM
qdyAmountZero,
tblAmount
GROUP BY
Abs([Combination]),
tblAmount.ID,
tblAmount.Amount,
qdyAmountZero.ID1,
qdyAmountZero.ID2,
qdyAmountZero.ID3,
qdyAmountZero.ID4,
qdyAmountZero.ID5,
qdyAmountZero.ID6
HAVING
(tblAmount.ID=1 AND qdyAmountZero.ID1=True)
OR
(tblAmount.ID=2 AND qdyAmountZero.ID2=True)
OR
(tblAmount.ID=3 AND qdyAmountZero.ID3=True)
OR
(tblAmount.ID=4 AND qdyAmountZero.ID4=True)
OR
(tblAmount.ID=5 AND qdyAmountZero.ID5=True)
OR
(tblAmount.ID=6 AND qdyAmountZero.ID6=True)
ORDER BY
Abs([Combination]),
tblAmount.ID;
</SQL>
This will display the (here two) selections and their associated rows
in a useful way:
Selection ID Amount
11 1 5,00
11 2 -2,00
11 4 -3,00
58 2 -2,00
58 4 -3,00
58 5 6,00
58 6 -1,00
For a real life application you would need to add some prefiltering
like those I have mentioned before like excluding positive values
exceeding the sum of the negative values and vice versa.
Have fun!
/gustav