Create expression using switch() in a Summation query

[scottc_00]

I have a query that creates an expression using a switch function and it works perfectly. What it does is combine 3 columns of data into one. I am now trying to use the exact same expression in another query and it is failing. The only difference is that the query it works in is a Simple Select query. The query it is failing in is a Summation query.



The summation query Groups ALL fields and sums (counts) the rows in each grouping.

This is the Switch()
HC_lvl678: Switch([Lvl 6]="Baseball","Baseball",[ Lvl 7]="Football","Football",[Lvl 7]="Basketball","Basketball",[Lvl 7]="Soccer","Soccer",[Lvl 8]="Hockey”,"Hockey",[Lvl 8]="Golf","Golf")

This is the Error I am getting
Syntax error (comma) in query expression ‘TableName.[ Switch([Lvl 6]="Baseball","Baseball",[ Lvl 7]="Football","Football",[Lvl 7]="Basketball","Basketball",[Lvl 7]="Soccer","Socc’.

Is it even possible to include an expression in a summation query?
Is it too long (it looks like its cut off but that may be a limitation in the mssg)?


Thanks

[June7]

Why do you have 8 fields with similar data? This is not normalized structure.

Do the expression in SELECT query then use that query as the source for aggregate query.

Or build a report and use its Grouping & Sorting features with aggregate calcs in header/footer sections. Report allows display of detail records as well as summary data.