All in all sum

  • Thread starter Thread starter PF
  • Start date Start date
P

PF

Hi all,

I'm trying to make a query that, based in a table
column "NAME", produces a sum of all its elements with all
the others, including combinations, that is:
NAME
A
B
C
..
..
..
N
And I wnat to get A+B, A+B+C, ..., A+B+C+...+N, A+C,
A+C+D, A+C+D+...+N.
I have tried a lot of ways, but can't get a way. Is this
impossible, or am I missing something?

Thanks in advance,

PF
 
Hi,


It would soon become unmanageable since you will got 2^n - 1 combinations (CRC Standard
Mathematical Tables and Formulae, section 3.2), if there is initially n rows... if you prefer, you
will get more than one giga-rows (1E9 rows) for n=30.


That being said, if you limit yourself, say, to four terms (ie, A+B+C+D, but not A+B+C+D+E, five
terms), start making a first query


SELECT amount, name FROM myTable
UNION
SELECT 0, NULL FROM myTable

saved as query qa, which just append a single row, ( 0, Null) to the initial set of rows (amount,
name).

Next, make the query (we limit ourself to four terms) :


SELECT a.amount + b.amount + c.amount + d.amount

FROM ( (myTable As a INNER JOIN qa As b
ON Nz(a.Name<b.Name , TRUE)

) INNER JOIN qa As c
ON (Nz(a.Name<c.Name, TRUE)
AND Nz(b.Name<c.Name, TRUE))

) INNER JOIN qa As d
ON (Nz(a.Name<d.Name, TRUE)
AND Nz(b.Name<d.Name, TRUE)
AND Nz(c.Name<d.Name, TRUE))


If we take a look at just the two alias a and b, the (a.name, b.name) for which the ON clause
evaluates to true, assuming the original names are 1 to m, is

(1, Null), (1, 2), (1, 3), ... (1, m), (2, Null), (2, 3), (2, 4), ...(2, m), (3, Null), (3, 4), (3,
5), .... (3, m), (4, Null), (4, 5), ... (m-1, m)

which is, in fact, picking any one or two names out of m, without repetition. The next joins
continue on the same idea. Since we are interested in a SUM, the order from which we pick up the
numbers is irrelevant, so the "less than" used in the ON clauses destroy the symmetry: picking (a,
then b) is the same as picking b then a, but < will remove that useless second case. It would have
not been appropriate to use it if the order of the picking would have been important, <> should
have been used, in such a case.


If you want include a fifth term,
add +e.amount at the end of the SELECT segment,
add a ( right after the key word FROM,
and append, to the end:

) INNER JOIN qa AS e
ON (Nz(a.Name<e.Name, TRUE)
AND Nz(b.Name<e.Name, TRUE)
AND Nz(c.Name<e.Name, TRUE)
AND Nz(d.Name<e.Name, TRUE)




Hoping it may help,
Vanderghast, Access MVP
 
Back
Top