As part of an aggregate function...

[Moxioron]

Hello all.

The SQL below is part of a query and works fine unless I place criteria (in red below). [qry_VISA Cardholder Transaction Detail - Merchant Rename Query_2].[Revised Category] is an expression and I can get the query to work if I select 'Totals' and 'Group By' in that query. However, the issue is that separate transactions are grouped together at times (all the fields are the same), which does not provide an accurate count.

How can I use criteria in an expression without having to Totals/Group By? Thank you.

SELECT [qry_VISA Cardholder Transaction Detail - Merchant Rename Query_2].Other, [qry_VISA Cardholder Transaction Detail - Merchant Rename Query_2].[Revised Category], Count([qry_VISA Cardholder Transaction Detail - Merchant Rename Query_2].[Transaction Amount]) AS [CountOfTransaction Amount], Sum([qry_VISA Cardholder Transaction Detail - Merchant Rename Query_2].[Transaction Amount]) AS [SumOfTransaction Amount], Sum([qry_VISA Cardholder Transaction Detail - Merchant Rename Query_2].[I/C Fee]) AS [SumOfI/C Fee], Sum([I/C Fee])/Sum([Transaction Amount]) AS [IC Rate]
FROM [qry_VISA Cardholder Transaction Detail - Merchant Rename Query_2]
GROUP BY [qry_VISA Cardholder Transaction Detail - Merchant Rename Query_2].Other, [qry_VISA Cardholder Transaction Detail - Merchant Rename Query_2].[Revised Category]
HAVING ((([qry_VISA Cardholder Transaction Detail - Merchant Rename Query_2].[Revised Category])=[Forms]![frm_VISA Cardholder Transaction Detail - Total By Merchant]![Category of the Merchant]));

 

[Brianwarnock]

I'm not sure that I understand your first paragraph but to answer this

How can I use criteria in an expression without having to Totals/Group By? Thank you.

Having clause apply criteria after aggregation , Where clauses before or when there is no aggregation.

However I suspect that we do not have the whole story here.

Brian