Sum all alternatives from column that equal specified value

[torz]

Hey guys,

No idea if this is possible or what to even search for to try and find out, been unsuccessful searching thus far...

I have a database with with 100s of values for a field. What I would like to be do is specify a value via a form and the query will return all options that equal the specified value.


so for example: Specified value = 150


Field 1 Field 2
------- -------

Name1 10
Name2 50
Name3 25
Name4 25
Name5 100
Name6 40
Name7 125
Name8 50


I'd like the query to return all possibilities that equal 150 so it should look like:

result 1 - name5 + name2
result 2 - Name5 + Name 8

etc etc etc

thanks guys!

 

[plog]

I can't build the whole thing for you right now so let me give you some advice and let you try to see if you can get it, if not I can give it a shot later.

You can do this with a Cartesian Product (http://en.wikipedia.org/wiki/Cartesian_product). Basically its a query of 2 (or more) tables that you don't join at all. By not joining them it bumps up every record in the first datasource against every record in the second datasource.

In practical terms, you need to build a query and bring in your table twice. For reference sake, let's call your table T which would make the second one T_1. Don't link them at all, and then bring down Field 1 and Field 2 from both T and T_1. Then create a calculated field like so:

T_SUM: T.[Field 2] + T_1.[Field 2]

Underneath that in the criteria section make it equal to 150.

 

[torz]

so easy

thanks !!

 

[plog]

No problem. The thing to be aware of is that you might have records finding themselves. Suppose Name7 has a value of 75. It's going to match up with itself and show in your query. You might need to add an additional criterion that says if the Field 1 value is the same it should be excluded.