Aggregates

[BeeJayEff]

I have a summary form which includes about a dozen fields referencing domain aggregate functions. Only one table is involved, but the form is getting very slow as that table grows. Each domain aggregate function has different criteria.
I would prefer to have the form bound to a query which gathered all the aggregate values together in one shot.
So for example :
Give me separate totals of FieldX :
1) in rows where FieldA = 6
2) in rows where FieldB = "XYZ"
3) in rows where FieldA <>6 and FieldB <>"XYZ"

Can I do that in a single query ?

 

[namliam]

yes (added characters to get past the 10 char limit)

 

[namliam]

Now to actually tell you how to do it

Select Sum(IIF(fieldA=6,1,0) as FieldAis6
, sum(IIF(FieldB="XYZ",1,0)) as FieldBisXYZ
, Sum(IIF(FieldA=6,0, IIF(FieldB="XYZ",0,1))) as Others
From YourTable