If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
List of values summing to a known value
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. |
#2
|
|||
|
|||
List of values summing to a known value
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. |
#3
|
|||
|
|||
List of values summing to a known value
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. |
#4
|
|||
|
|||
List of values summing to a known value
On Sat, 29 May 2010 13:04:47 -0400, John Spencer 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] |
Thread Tools | |
Display Modes | |
|
|