Append Query error: "query does not include specified expression..as part of aggregate function"

[louise]

Hello,


I have created an append query. All looks fine when I view the data, but when trying to do the append, I get the following error "Your query does not include the specified expression "AllCurricID" as part of an aggregate function. (And removing that field simply shifts the message to the next field.)

I have one field (TBLJuncMembCourseSelections.CoursesChosen.Value) that includes a string of AllCurricIDs and want that to be counted and appended as the NumberEnrolled, but that does not seem to be the problem.

I am working from the query design, but have included the SQL code below.

Thanks for any help you can give me! I hope this is enough information.

INSERT INTO [TBLArchive-PastCourseInfo] ( AllCurricID, ArchivedSemester, ArchivedYear, CourseCode, CourseTitleFinal, PresenterName, SingleOrMulti, BasedOnDVDs, NumberEnrolled )
SELECT TBLOverallCurric.AllCurricID, TBLJuncMembCourseSelections.Semester, TBLJuncMembCourseSelections.Year, TblCourseDetails.CourseCode, TBLOverallCurric.CourseTitleFinal, TBLOverallCurric.PresenterName, TBLOverallCurric.SingleOrMulti, TBLOverallCurric.BasedOnDVDs, Count(TBLJuncMembCourseSelections.CoursesChosen.Va lue) AS CountOfCoursesChosen_Value
FROM (TBLOverallCurric INNER JOIN TBLJuncMembCourseSelections ON TBLOverallCurric.AllCurricID = TBLJuncMembCourseSelections.CoursesChosen.Value) INNER JOIN TblCourseDetails ON TBLOverallCurric.AllCurricID = TblCourseDetails.AllCurricID
GROUP BY TBLOverallCurric.AllCurricID, TBLJuncMembCourseSelections.Semester, TBLJuncMembCourseSelections.Year, TblCourseDetails.CourseCode, TBLOverallCurric.CourseTitleFinal, TBLOverallCurric.PresenterName, TBLOverallCurric.SingleOrMulti, TBLOverallCurric.BasedOnDVDs;

[June7]

Is CoursesChosen a multi-value field? Multi-value field is not a 'string'. I NEVER use multi-value fields. Do you have a complete understanding of what they are? Review https://support.office.com/en-us/art...C-6DE9BEBBEC31

I think will have to do a separate aggregate query to expand the multi-value field and do a Count (by what - AllCurricID?). Then include that query in the nested SELECT for the UPDATE with no GROUP BY there.

However, it is not advisable to save aggregate data - calculate when needed. Any UPDATE query that can successfully save the data could just as well be a SELECT query that presents the value when needed.

Year is a reserved word (an intrinsic function) and reserved words should not be used as names. If used, then enclose in []. Although, in some situations even this is not enough to prevent unexpected results.