Access query help - results as string or list

[heckstein]

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.LEARNINGACTIVITYCODE,3) AS CATEGORY, I.LEARNINGACTIVITYTITLE, I.LEARNINGACTIVITYCODE, I.CODE, I.STARTDATE, I.ENDDATE, I.UNITS, Count(T.ENROLLMENTSTATUS) AS COMPLETED, I.UNITS*Count(T.ENROLLMENTSTATUS) AS HOURS, Format(I.UNITS*Count(T.ENROLLMENTSTATUS)/8, "000.000") AS DAYS, T.ENROLLMENTSTATUSDESCR, T.STUDENTCOMMENTS, N.INSTRUCTORID, R.LASTNAME AS INSTRUCTOR
FROM LMS650_DRLAINSTANCEFULL AS I, LMS650_DRLATRANSCRIPTALT AS T, LMS650_DRLAIINSTRUCTORMAP AS M, LMS650_DRLAINSTANCEFULLINSTRUCTORS AS N, LMS650_DRRESOURCEFULL AS R

WHERE (((( I.LEARNINGACTIVITYID=T.LEARNINGACTIVITYID) AND I.LEARNINGACTIVITYCODE=T.LACODE) AND I.CODE=T.INSTANCECODE) AND T.LEARNINGACTIVITYINSTANCEID=M.LEARNINGACTIVITYINS TANCEID) AND ((M.INSTRUCTORID=N.INSTRUCTORID) AND N.INSTRUCTORID=R.INSTRUCTORID) AND I.LEARNINGACTIVITYCODE Like 'ODI%' AND (( I.STARTDATE) >= #1/1/2007#) AND (( I.STARTDATE) < #11/1/2007#) AND ((I.STATUS)='A') AND T.ENROLLMENTSTATUS='C' AND T.ENROLLMENTSTATUSDESCR='Completed'

GROUP BY I.LEARNINGACTIVITYTITLE, I.LEARNINGACTIVITYCODE, I.CODE, I.STARTDATE, I.ENDDATE, I.UNITS, I.ENROLLEDCOUNT, T.ENROLLMENTSTATUS, T.ENROLLMENTSTATUSDESCR, T.STUDENTCOMMENTS, N.INSTRUCTORID, R.LASTNAME
ORDER BY I.LEARNINGACTIVITYCODE, I.CODE, I.LEARNINGACTIVITYTITLE, I.STARTDATE DESC;

Thank you for any suggestions.

[JustJim]

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.LEARNINGACTIVITYCODE,3) AS CATEGORY, I.LEARNINGACTIVITYTITLE, I.LEARNINGACTIVITYCODE, I.CODE, I.STARTDATE, I.ENDDATE, I.UNITS, Count(T.ENROLLMENTSTATUS) AS COMPLETED, I.UNITS*Count(T.ENROLLMENTSTATUS) AS HOURS, Format(I.UNITS*Count(T.ENROLLMENTSTATUS)/8, "000.000") AS DAYS, T.ENROLLMENTSTATUSDESCR, T.STUDENTCOMMENTS, N.INSTRUCTORID, R.LASTNAME AS INSTRUCTOR
FROM LMS650_DRLAINSTANCEFULL AS I, LMS650_DRLATRANSCRIPTALT AS T, LMS650_DRLAIINSTRUCTORMAP AS M, LMS650_DRLAINSTANCEFULLINSTRUCTORS AS N, LMS650_DRRESOURCEFULL AS R

WHERE (((( I.LEARNINGACTIVITYID=T.LEARNINGACTIVITYID) AND I.LEARNINGACTIVITYCODE=T.LACODE) AND I.CODE=T.INSTANCECODE) AND T.LEARNINGACTIVITYINSTANCEID=M.LEARNINGACTIVITYINS TANCEID) AND ((M.INSTRUCTORID=N.INSTRUCTORID) AND N.INSTRUCTORID=R.INSTRUCTORID) AND I.LEARNINGACTIVITYCODE Like 'ODI%' AND (( I.STARTDATE) >= #1/1/2007#) AND (( I.STARTDATE) < #11/1/2007#) AND ((I.STATUS)='A') AND T.ENROLLMENTSTATUS='C' AND T.ENROLLMENTSTATUSDESCR='Completed'

GROUP BY I.LEARNINGACTIVITYTITLE, I.LEARNINGACTIVITYCODE, I.CODE, I.STARTDATE, I.ENDDATE, I.UNITS, I.ENROLLEDCOUNT, T.ENROLLMENTSTATUS, T.ENROLLMENTSTATUSDESCR, T.STUDENTCOMMENTS, N.INSTRUCTORID, R.LASTNAME
ORDER BY I.LEARNINGACTIVITYCODE, I.CODE, I.LEARNINGACTIVITYTITLE, I.STARTDATE DESC;

Thank you for any suggestions.

You are not (easily) going to get a query to do what you want. What you need to do is make sure your query is gathering all the required records (which it sounds like it is) and use that query as the record source for either a form or a report and do the grouping on the form or report. Yell for help if you need it!

Jim

[heckstein]

Thank you for the help. I will take that approach since my final output is a report.

[JustJim]

Thank you for the help. I will take that approach since my final output is a report.

No worries Mate!

JIm