By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,906 Members | 1,771 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,906 IT Pros & Developers. It's quick & easy.

Access query help - results as string or list

P: 10
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.
Nov 6 '07 #1
Share this Question
Share on Google+
3 Replies


JustJim
Expert 100+
P: 407
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
Nov 7 '07 #2

P: 10
Thank you for the help. I will take that approach since my final output is a report.
Nov 7 '07 #3

JustJim
Expert 100+
P: 407
Thank you for the help. I will take that approach since my final output is a report.
No worries Mate!

JIm
Nov 8 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.