List of values summing to a known value

  • Thread starter Thread starter lmattern
  • Start date Start date
L

lmattern

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.
 
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));
 
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
 
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.
Click to expand...

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.
 
Back
Top