CODEWITHSUNDEEP
sqlms-access
AnswerMe
AnswerMe

Reputation: 13

Access Update Query: Query does not include the specified expression as part of an aggregate function

I'm trying to do the following: 1. Put the count of each drivers' cancellation from the Ride table (IsCancelled = "Y") into the Driver table to keep track of how many cancellations each driver has made. 2. Input the average rating of each driver into the Driver table - which is derived from the average of all DriverRating for that specific driver from the Ride table. 

UPDATE Driver 

INNER JOIN ((Car 
INNER JOIN CarTypeAssignment ON Car.LicensePlateNo = CarTypeAssignment.LicensePlateNo) 
INNER JOIN Ride ON CarTypeAssignment.CarTypeAssignmentID = Ride.CarTypeAssignmentID) 

ON Driver.DriverLicenseNo = Car.DriverLicenseNo 

SET Driver.DriverCancellation = Count([Ride].[IsCancelled]), Driver.DriverRating = Avg([Ride].[DriverRating]);

I tried to execute the SQL code above in Microsoft Access, but got the following error message:

Your query does not include the specified expression 'Driver Cancellation' as part of an aggregate function.

*P.s. I am unable to attach picture of my tables, so please ignore the INNER JOIN portion of the code - it should be correct.

Upvotes: 0

Views: 309

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

UPDATE doesn't support aggregation. You need to use a subquery.

UPDATE Driver d INNER JOIN
       (SELECT DriverLicenseNo, 
               Count([Ride].IsCancelled) as cnt,
               Avg([Ride].[DriverRating]) as avg_rating
        FROM (Car as c INNER JOIN
              CarTypeAssignment as cta
              ON c.LicensePlateNo = cta.LicensePlateNo
             ) INNER JOIN
             Ride as r
             ON cta.CarTypeAssignmentID = r.CarTypeAssignmentID
       ) as c 
       ON d.DriverLicenseNo = c.DriverLicenseNo 
    SET d.DriverCancellation = cnt,
        d.DriverRating = avg_rating;

Upvotes: 0

Related Questions