I have created a query in MS Access 2003 that is pulling training records for our company that includes training hour calculation. One field I am pulling is the instructor name. Many courses have multiple instructors, which means I am getting multiple records for a single course. I need to capture all of the instructor names, but I only want a single record for each course due to the hour calculation. I would like the report to generate only one record per unique course but would like all of the instructor names to appear in a single field as a list. I am sure there is a way to do this, but I am not sure how. A coworker suggested it has to do with how I group. It is the R.LASTNAME that I would like to list in 1 field. Here is the query I am using:
SELECT Left(I.LEARNING ACTIVITYCODE,3) AS CATEGORY, I.LEARNINGACTIV ITYTITLE, I.LEARNINGACTIV ITYCODE, I.CODE, I.STARTDATE, I.ENDDATE, I.UNITS, Count(T.ENROLLM ENTSTATUS) AS COMPLETED, I.UNITS*Count(T .ENROLLMENTSTAT US) AS HOURS, Format(I.UNITS* Count(T.ENROLLM ENTSTATUS)/8, "000.000") AS DAYS, T.ENROLLMENTSTA TUSDESCR, T.STUDENTCOMMEN TS, N.INSTRUCTORID, R.LASTNAME AS INSTRUCTOR
FROM LMS650_DRLAINST ANCEFULL AS I, LMS650_DRLATRAN SCRIPTALT AS T, LMS650_DRLAIINS TRUCTORMAP AS M, LMS650_DRLAINST ANCEFULLINSTRUC TORS AS N, LMS650_DRRESOUR CEFULL AS R
WHERE (((( I.LEARNINGACTIV ITYID=T.LEARNIN GACTIVITYID) AND I.LEARNINGACTIV ITYCODE=T.LACOD E) AND I.CODE=T.INSTAN CECODE) AND T.LEARNINGACTIV ITYINSTANCEID=M .LEARNINGACTIVI TYINS TANCEID) AND ((M.INSTRUCTORI D=N.INSTRUCTORI D) AND N.INSTRUCTORID= R.INSTRUCTORID) AND I.LEARNINGACTIV ITYCODE Like 'ODI%' AND (( I.STARTDATE) >= #1/1/2007#) AND (( I.STARTDATE) < #11/1/2007#) AND ((I.STATUS)='A' ) AND T.ENROLLMENTSTA TUS='C' AND T.ENROLLMENTSTA TUSDESCR='Compl eted'
GROUP BY I.LEARNINGACTIV ITYTITLE, I.LEARNINGACTIV ITYCODE, I.CODE, I.STARTDATE, I.ENDDATE, I.UNITS, I.ENROLLEDCOUNT , T.ENROLLMENTSTA TUS, T.ENROLLMENTSTA TUSDESCR, T.STUDENTCOMMEN TS, N.INSTRUCTORID, R.LASTNAME
ORDER BY I.LEARNINGACTIV ITYCODE, I.CODE, I.LEARNINGACTIV ITYTITLE, I.STARTDATE DESC;
Thank you for any suggestions.
SELECT Left(I.LEARNING ACTIVITYCODE,3) AS CATEGORY, I.LEARNINGACTIV ITYTITLE, I.LEARNINGACTIV ITYCODE, I.CODE, I.STARTDATE, I.ENDDATE, I.UNITS, Count(T.ENROLLM ENTSTATUS) AS COMPLETED, I.UNITS*Count(T .ENROLLMENTSTAT US) AS HOURS, Format(I.UNITS* Count(T.ENROLLM ENTSTATUS)/8, "000.000") AS DAYS, T.ENROLLMENTSTA TUSDESCR, T.STUDENTCOMMEN TS, N.INSTRUCTORID, R.LASTNAME AS INSTRUCTOR
FROM LMS650_DRLAINST ANCEFULL AS I, LMS650_DRLATRAN SCRIPTALT AS T, LMS650_DRLAIINS TRUCTORMAP AS M, LMS650_DRLAINST ANCEFULLINSTRUC TORS AS N, LMS650_DRRESOUR CEFULL AS R
WHERE (((( I.LEARNINGACTIV ITYID=T.LEARNIN GACTIVITYID) AND I.LEARNINGACTIV ITYCODE=T.LACOD E) AND I.CODE=T.INSTAN CECODE) AND T.LEARNINGACTIV ITYINSTANCEID=M .LEARNINGACTIVI TYINS TANCEID) AND ((M.INSTRUCTORI D=N.INSTRUCTORI D) AND N.INSTRUCTORID= R.INSTRUCTORID) AND I.LEARNINGACTIV ITYCODE Like 'ODI%' AND (( I.STARTDATE) >= #1/1/2007#) AND (( I.STARTDATE) < #11/1/2007#) AND ((I.STATUS)='A' ) AND T.ENROLLMENTSTA TUS='C' AND T.ENROLLMENTSTA TUSDESCR='Compl eted'
GROUP BY I.LEARNINGACTIV ITYTITLE, I.LEARNINGACTIV ITYCODE, I.CODE, I.STARTDATE, I.ENDDATE, I.UNITS, I.ENROLLEDCOUNT , T.ENROLLMENTSTA TUS, T.ENROLLMENTSTA TUSDESCR, T.STUDENTCOMMEN TS, N.INSTRUCTORID, R.LASTNAME
ORDER BY I.LEARNINGACTIV ITYCODE, I.CODE, I.LEARNINGACTIV ITYTITLE, I.STARTDATE DESC;
Thank you for any suggestions.
Comment