access sql query aggregate function

Question

I m trying to run a sql in access 2007 which gives the error

"you tried to execute a query that does not include the specified expression 'round(volumePass/Volume*100,2)' as part of an aggregate function".

Whereas i have mentioned it in the sql statement

group by round(volumePass/Volume*100,2)

SELECT s.[sub process name], Round(Avg(s.[TollGate FTEs]),2) AS TollGateFTEs, 
Sum(w.volume) AS Volume, 
Sum(Switch(w.TAT='Pass',w.Volume,w.TAT='Fail',0)) AS VolumePass, 
Sum(IIf(w.[combined accuracy]='PASS',1,0)) AS AccuracyPass, 
Sum(IIf(w.[combined accuracy]='',0,1)) AS TotalAccuracy, 
Round((VolumePass/Volume)*100,2) AS TATPercentage, 
Round((AccuracyPass/TotalAccuracy)*100,2) AS AccuracyPercentage, 
Format(w.[reporting month],'mmm-yy') AS [Rep Month] FROM Work_Log1 AS w, 
[sub process] AS s WHERE w.[sub process] In (SELECT s.[sub_process_id] FROM 
[Sub Process] s, 
[process mapping] m where m.[process name] like 'Accounting Control%'  
and s.[mapped to process id] = m.[mapping id]) And w.[sub process]=[s].[sub_process_id] 
AND (w.[Activity_start_date] Between #06/01/2012# And #06/15/2012#)  
AND ([w].[sla applicable]=1 Or 0 Or '') and 
(w.[status] Like 'Complete%')  group BY Format(w.[reporting month],'mmm-yy'), 
s.[sub process name], (Round((VolumePass/Volume)*100,2));

Where is that it is not able to pick up correctly.

giving the alias "TATPercentage" still not works and gives the same error.

This query works well in access query designer but cannot be run with sql inside vba.

Update:

Got it to run using the dao engine in vba.. no luck with adodb engine

Marc B · Accepted Answer · 2012-10-15 15:30:08Z

1

Round((VolumePass/Volume)*100,2) AS TATPercentage
       ^^^^^^^^^^ ^^^^^^

round() is not an aggregate function. You'll have to group on those fields as well (volumepass and volume), and you'll run into the same error with your next two lines with accuracypass, acuracytotal, reporting month, etc...

answered Oct 15, 2012 at 15:30

Marc B

362k44 gold badges433 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Lynn Crumbling Over a year ago

My answer simply fixes his group by issue... but your answer raises a valid question -- does he REALLY want to group on that percentage, or on the values that compose that value.

Collectives™ on Stack Overflow

access sql query aggregate function

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related