Using a variable in the Field of a query

[Steven]

I would like to make a query where can use a variable to
tell one of the fields what I want it to do. The ultimate
goal of the query is to Sum() fields in a table. The
problem is that depending on the period the user wants to
sum through the Sum() expression will change. For
example: The user wants the sum Jan - Feb the expression
would be Sum([101]+[102]). Jan - June it would be Sum
([101]+[102]+[103]+[104]+[105]+[106]). Is there a way you
can pass a variable with the Sum() function expression to
a query?


The query would look something like:

The ????? is what I am trying to do. Can I pass a string
variable or some kind of variable to here to tell which
fields I want to total?

Field: Company Account ?????
Table: Balances Balances Balances
Total: Group By Group By Expression
Sort: Ascending Ascending
Show
Criteria:

Thank you for your help.

If I am totally off base is there a solution to this.

Thanks.

 

[Tom Ellison]

Dear Steven:

Maybe it's how you expressed yourself, but I'm worried about the
structure of the data on which you're doing this.

You said you want "to Sum() fields in a table"

You cannot Sum() fields. You can Sum() a single field across many
rows in the query. Is that what you actually mean? Or have you
created multiple columns and want to sum them as though they were some
sort of array?

And just what are these values [101], [102], etc. Are these
references to different columns of data, or are they partial keys to
various rows?

If you have structured the Months into multiple columns, you have
created a monster, and it's going to take serious remedial work to get
anything functioning.

So please lay out the data from which you want to build this.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[John Spencer (MVP)]

Steven,

You MIGHT be able to use an expression to do this, but if you can I think you
really, really, really need to restructure your tables and relationships.

Field: TotalUP:
[101] +
IIF([How Many]>1,[102],0) +
IIF([How Many]>2,[103],0) +
IIF([How Many]>3,[104],0) +
...
Table: <Blank>
Total: Expression
Sort:
Show:
Criteria:

Since you didn't say how you were getting the expression, I assummed the easiest
method of using a parameter query and just assumed a standard number of months
from one to twelve. I also guessed that you have fields named 101, 102, 103, etc.

This would be a lot simpler if you had a table with two fields in it, one field
to hold your current field names and a second to hold the values of your current
fields or even a table structured like:

TableBalances:
CompanyID:
AccountID:
DateRange: 101,102,103,104,...
Amount: Number of Dollars

Then the query would be

SELECT CompanyID, AccountID, SUM(Amount) as Total
FROM tableBalances
WHERE DateRange Between "101" and "104"
GROUP BY CompanyID, AccountID
ORDER BY CompanyID, AccountID