A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

List of values summing to a known value



 
 
Thread Tools Display Modes
  #1  
Old May 28th, 2010, 09:04 PM posted to microsoft.public.access
lmattern
external usenet poster
 
Posts: 3
Default 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  
Old May 28th, 2010, 09:42 PM posted to microsoft.public.access
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default 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  
Old May 29th, 2010, 06:04 PM posted to microsoft.public.access
John Spencer
external usenet poster
 
Posts: 7,815
Default 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  
Old June 1st, 2010, 06:14 PM posted to microsoft.public.access
John W. Vinson
external usenet poster
 
Posts: 18,261
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 08:39 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.