the EVIL aggregate function

[OTWarrior via AccessMonster.com]

I have a query that works fine, and I need to "sum" the ammount of ones that
appear in 2 sections.

I use another query to calculate this (obviously), but it doesn't work when I
have anything else other that the 2 expression.

here is the sql:

GROUP BY qry_EXP_RES_monthlyRPT1.TheProvider, Sum(IIf([Accepted]="Accepted",1,
0)), Sum(IIf([Accepted]="refused",1,0))

the provider part breaks it.

if I include a group by (which is mentioned else where on this forum) then I
get the mesage (or similar)
"cannot have aggreate in group by by clause"

here is the sql for that:

SELECT qry_EXP_RES_monthlyRPT1.TheProvider, Sum(IIf([Accepted]="Accepted",1,0)
) AS "Requested Accepted", Sum(IIf([Accepted]="refused",1,0)) AS "Request
Refused"
FROM qry_EXP_RES_monthlyRPT1
GROUP BY qry_EXP_RES_monthlyRPT1.TheProvider, Sum(IIf([Accepted]="Accepted",1,
0)), Sum(IIf([Accepted]="refused",1,0))
ORDER BY qry_EXP_RES_monthlyRPT1.TheProvider;


what am I doing wrong?

 

[John Spencer]

I would think the following would work.

SELECT qry_EXP_RES_monthlyRPT1.TheProvider
, Sum(IIf([Accepted]="Accepted",1,0)) AS [Requested Accepted]
, Sum(IIf([Accepted]="refused",1,0)) AS [Request Refused]
FROM qry_EXP_RES_monthlyRPT1
GROUP BY qry_EXP_RES_monthlyRPT1.TheProvider
ORDER BY qry_EXP_RES_monthlyRPT1.TheProvider;

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..