David Smith

David Smith

  • NA
  • 2k
  • 0

SQL Subquery Error: cannot have memo, ole, hyperlinks object fields in aggregate functions,

Sep 23 2012 4:12 AM
I need help with query below, I am getting error that is saying you cannot have memo, ole, hyperlinks object fields in  aggregate functions, how to
get pass this error look at the below query, I am using Access 2007 at the moment. look at the subquery in purple


                         Select refServiceStatus.ServiceStatus, tblService.ServiceDateStart,

                         (SELECT MAX(tblContact.Comments) FROM tblContact WHERE tblContact.ContactID = tblService.FilledByID) AS VolunteerDriveComments
                         FROM ((((((((tblService INNER JOIN tblContact ON tblService.RequestByID = tblContact.ContactID) INNER JOIN refServiceStatus ON tblService.ServiceStatus =  refServiceStatus.ServiceStatusID) INNER JOIN
                         refCityLocation ON tblContact.Location = refCityLocation.LocationID) INNER JOIN refDestination ON tblService.ServiceTo = refDestination.DestinationID) INNER JOIN  refServiceType ON tblService.ServiceType = refServiceType.ServiceTypeID) INNER JOIN refPriority ON tblService.PriorityID = refPriority.PriorityID)
                         LEFT JOIN tblServiceEvent ON tblService.ServiceID = tblServiceEvent.ServiceID) LEFT JOIN refServiceEventType ON tblServiceEvent.ServiceEventType =  refServiceEventType.ServiceEventTypeID)       
                         Where tblService.ServiceID = 54575