[AccessD] Math Problem

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



More information about the AccessD mailing list