Summing Columns

[Guest]

I'm having a query where I sum a number of records using an IIf function
which gives me a dynaset looking like this;

Budget 0501 Forecast 0501 Outcome 0501
Budget 0502 ..
Account 1
Account 2

Now I want to sum the budget coulmns by simply adding a new column using the
expression;
Budget Q1: Sum([Budget 0501]+[Budget 0502]+[Budget 0503])
but I can't get this expression to work and I receive a message that
subqueries cannot be used in the expression.

What do I do wrong?
Best Regards / Bell

 

[Guest]

Try writing two queries: one where you do the first calc, then use that query
as the basis of the second query that performs additional calcs on the
results of the first query.

When you run query #2, it will automatically call query #1 as its basis, but
display only the final results. Sometimes putting everything into one query
just gets too complex, even for Access.

 

[Duane Hookom]

It might help if you provided your table structure and the SQL of your query
with the IIf()s.

 

[Guest]

Hi Brian,
tried putting it in two queries as you suggested, I no longer get any
warnings but wont get any results either. Could there be something else wrong.

Best regards / Bell

"Brian" skrev:

Try writing two queries: one where you do the first calc, then use that query
as the basis of the second query that performs additional calcs on the
results of the first query.

When you run query #2, it will automatically call query #1 as its basis, but
display only the final results. Sometimes putting everything into one query
just gets too complex, even for Access.

I'm having a query where I sum a number of records using an IIf function
which gives me a dynaset looking like this;

Budget 0501 Forecast 0501 Outcome 0501
Budget 0502 ..
Account 1
Account 2

Now I want to sum the budget coulmns by simply adding a new column using the
expression;
Budget Q1: Sum([Budget 0501]+[Budget 0502]+[Budget 0503])
but I can't get this expression to work and I receive a message that
subqueries cannot be used in the expression.

What do I do wrong?
Best Regards / Bell