Summing 2 subqueries

[bd528]

Hi,



I'm using the SQL below in Access 2010 :-

SELECT tblMerged.Col4, 
(select sum(dupe.col8)
from tblMerged as dupe
where dupe.col4 = tblMerged.col4
and dupe.col2 = "I"
group by col8) AS Expr1, 
(select sum(dupe.col12)
from tblMerged as dupe
where dupe.col4 = tblMerged.col4
and dupe.col2 = "U"
group by col12) AS Expr2, 
(select sum([dupe.col11])
from tblMerged as dupe
where dupe.col4 = tblMerged.col4
and dupe.col2 = "C") AS Expr3
FROM tblMerged
GROUP BY tblMerged.Col4;

What do I need to add to this so that I can have another column showing Expr2 & Expr3 added together?

Many thanks.

[June7]

Did you try: Expr2 + Expr3 As Total

If that doesn't work, I suspect the query has to repeat the subqueries in the expression.

Suggest you build a report with this query as RecordSource and do the sum in textbox ControlSource: =Expr2 + Expr3

[bd528]

That's a great idea, as I was planning to pull the results through to a report anyway.

What would be the best approach of only showing results where Expr2 + Expr3 = Expr1?

[June7]

You want to filter records? Criteria under the constructed Total column: Expr1

Or include all records but just don't show value if the expression is not true?
IIf(Expr2 + Expr3 = Expr1, Expr2 + Expr3, Null)

[bd528]

Thanks again, sorry I'm pretty new to queries. What would the criteria expression be for Expr1 in the query?

[June7]

No, Expr1 IS the criteria, under the Total column.

Oh, wait Expr1 is also result of calc (doh!) - in this case, again a subquery. Again, a field created by expression cannot be referenced in WHERE clause. Must repeat the expression. This is getting messy.

[bd528]

Is there is another option rather than using subqueries?

[June7]

Don't know because don't really know your data structure nor what you are trying to accomplish for output. Maybe you are trying to do too much in query and should be building a report that uses Grouping & Sorting with aggregate calcs in header/footer.

[bd528]

You may be right. I think, in the short term, I'll create another query based on the original and do filtering via that.
But thank you for your help!