Prev: Changing field names
Next: work
From: lmattern on 28 May 2010 16:04
Is it possible to run a query that would return a list of values that sum to
a known quantity. A simplified example would be as follows:

Return any items from the list that sum to 10

1
3
4
7
9

The returned values would be 1,9 and 3,7.

Thanks.


From: KARL DEWEY on 28 May 2010 16:42
Do you mean something like this --
SELECT [TableA_1].[Auto]+[TableA].[Auto] AS Expr1, TableA.Auto, TableA_1.Auto
FROM TableA, TableA AS TableA_1
WHERE ((([TableA_1].[Auto]+[TableA].[Auto])=10));

--
Build a little, test a little.


"lmattern" wrote:

> Is it possible to run a query that would return a list of values that sum to
> a known quantity. A simplified example would be as follows:
>
> Return any items from the list that sum to 10
>
> 1
> 3
> 4
> 7
> 9
>
> The returned values would be 1,9 and 3,7.
>
> Thanks.
>
>
From: John Spencer on 29 May 2010 13:04
Only two numbers? What about 3 numbers or more (1 + 2 + 7 = 10 assuming you
had 1,2, and 7 in the list)?

Assumption: Number values in the table are unique.

Basic query would be:
SELECT A.NumberField, B.NumberField
FROM NumbersTable As A, NumbersTable As B
WHERE A.NumberField < B.NumberField
AND A.NumberField + B.NumberField = 10

The first filter criterion is to eliminate duplicates 1,9 and 9,1.

You could do something similar with 3 numbers.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

lmattern wrote:
> Is it possible to run a query that would return a list of values that sum to
> a known quantity. A simplified example would be as follows:
>
> Return any items from the list that sum to 10
>
> 1
> 3
> 4
> 7
> 9
>
> The returned values would be 1,9 and 3,7.
>
> Thanks.
>
>
From: John W. Vinson on 1 Jun 2010 13:14
On Sat, 29 May 2010 13:04:47 -0400, John Spencer <spencer(a)chpdm.edu> wrote:

>Only two numbers? What about 3 numbers or more (1 + 2 + 7 = 10 assuming you
>had 1,2, and 7 in the list)?
>
>Assumption: Number values in the table are unique.
>
>Basic query would be:
>SELECT A.NumberField, B.NumberField
>FROM NumbersTable As A, NumbersTable As B
>WHERE A.NumberField < B.NumberField
>AND A.NumberField + B.NumberField = 10
>
>The first filter criterion is to eliminate duplicates 1,9 and 9,1.
>
>You could do something similar with 3 numbers.

But of course if there is an arbitrary number of members of the set, you have
the celebrated "Knapsack Problem", a member of the NP-Complete problems. It's
all but certain that there is no *efficient* general solution, and that as the
number of members of the set increases the solution becomes exponentially
harder to reach.
--

John W. Vinson [MVP]
 | 
Pages: 1
Prev: Changing field names
Next: work