CODEWITHSUNDEEP
sqlms-access-2007
Pradyut Bhattacharya
Pradyut Bhattacharya

Reputation: 5748

access sql query aggregate function

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

Upvotes: 0

Views: 1539

Answers (1)

Marc B
Marc B

Reputation: 360572

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...

Upvotes: 1

Related Questions