I am working in Access 2002 and trying to create a report from our company's learming management system. I am not a DBA and most of my SQL knowledge has been self taught through trial and error. I have created an access query to track the number of training hours for a training group. The query is working except for one piece of data and I hoping someone can help me. There is a field titled enrollment status, which presents an alpha character of C, E or N, all in one column. I would like to present this information in 3 columns (one for each status code) as a count vs the actual data. I have sucessfully created the 3 columns and am able to get a count of one of the values. However, I end up with the same count in each column. My query includes multiple "union all" statements as wells because I have multiple course categories that I need to combine into one report. Here is the code for the first group.
SELECT I.LEARNINGACTIV ITYTITLE, I.LEARNINGACTIV ITYCODE, I.CODE, I.STARTDATE, I.ENDDATE, I.UNITS, I.ENROLLEDCOUNT , COUNT (T.ENROLLMENTST ATUS) AS COMPLETED, COUNT ( T.ENROLLMENTSTA TUS) AS ENROLLED, COUNT (T.ENROLLMENTST ATUS) AS SKIPPED
FROM LMS650_DRLAINST ANCEFULL AS I, LMS650_DRLATRAN SCRIPTFULL AS T
WHERE T.LEARNINGACTIV ITYID=I.LEARNIN GACTIVITYID AND T.INSTANCECODE= I.CODE AND I.LEARNINGACTIV ITYCODE LIKE 'ODE%' AND ((I.STARTDATE) Between #8/1/2006# And #8/31/2006#) AND T.ENROLLMENTSTA TUS='C'
GROUP BY I.LEARNINGACTIV ITYTITLE, I.LEARNINGACTIV ITYCODE, I.CODE, I.STARTDATE, I.ENDDATE, I.UNITS, I.ENROLLEDCOUNT
UNION ALL SELECT I.LEARNINGACTIV ITYTITLE, I.LEARNINGACTIV ITYCODE, I.CODE, I.STARTDATE, I.ENDDATE, I.UNITS, I.ENROLLEDCOUNT , COUNT (T.ENROLLMENTST ATUS) AS COMPLETED, COUNT ( T.ENROLLMENTSTA TUS) AS ENROLLED, COUNT (T.ENROLLMENTST ATUS) AS NO_SHOW
FROM LMS650_DRLAINST ANCEFULL AS I, LMS650_DRLATRAN SCRIPTFULL AS T
WHERE T.LEARNINGACTIV ITYID=I.LEARNIN GACTIVITYID AND T.INSTANCECODE= I.CODE AND I.LEARNINGACTIV ITYCODE LIKE 'ODE%' AND ((I.STARTDATE) Between #8/1/2006# And #8/31/2006#) AND T.ENROLLMENTSTA TUS='E'
GROUP BY I.LEARNINGACTIV ITYTITLE, I.LEARNINGACTIV ITYCODE, I.CODE, I.STARTDATE, I.ENDDATE, I.UNITS, I.ENROLLEDCOUNT
UNION ALL SELECT I.LEARNINGACTIV ITYTITLE, I.LEARNINGACTIV ITYCODE, I.CODE, I.STARTDATE, I.ENDDATE, I.UNITS, I.ENROLLEDCOUNT , COUNT (T.ENROLLMENTST ATUS) AS COMPLETED, COUNT ( T.ENROLLMENTSTA TUS) AS ENROLLED, COUNT (T.ENROLLMENTST ATUS) AS NO_SHOW
FROM LMS650_DRLAINST ANCEFULL AS I, LMS650_DRLATRAN SCRIPTFULL AS T
WHERE T.LEARNINGACTIV ITYID=I.LEARNIN GACTIVITYID AND T.INSTANCECODE= I.CODE AND I.LEARNINGACTIV ITYCODE LIKE 'ODE%' AND ((I.STARTDATE) Between #8/1/2006# And #8/31/2006#) AND T.ENROLLMENTSTA TUS='N'
GROUP BY I.LEARNINGACTIV ITYTITLE, I.LEARNINGACTIV ITYCODE, I.CODE, I.STARTDATE, I.ENDDATE, I.UNITS, I.ENROLLEDCOUNT
ORDER BY I.LEARNINGACTIV ITYCODE, I.CODE, I.LEARNINGACTIV ITYTITLE, I.STARTDATE DESC;
As I indicated, the query is working correctly and pulling correct data except for the count. I am getting the correct count for the first select statement in the completed column, but the count for the completed column also appears in the enrolled column and the skipped column. I read something about counting horizontally vs vertically and tried a number of things including the case function, but have not been successful. We are on Windows 2002 professional. The DB is an oracle DB. I apologize if I am not presenting my question clearly, but this is my first post to a forum. Thank you.
SELECT I.LEARNINGACTIV ITYTITLE, I.LEARNINGACTIV ITYCODE, I.CODE, I.STARTDATE, I.ENDDATE, I.UNITS, I.ENROLLEDCOUNT , COUNT (T.ENROLLMENTST ATUS) AS COMPLETED, COUNT ( T.ENROLLMENTSTA TUS) AS ENROLLED, COUNT (T.ENROLLMENTST ATUS) AS SKIPPED
FROM LMS650_DRLAINST ANCEFULL AS I, LMS650_DRLATRAN SCRIPTFULL AS T
WHERE T.LEARNINGACTIV ITYID=I.LEARNIN GACTIVITYID AND T.INSTANCECODE= I.CODE AND I.LEARNINGACTIV ITYCODE LIKE 'ODE%' AND ((I.STARTDATE) Between #8/1/2006# And #8/31/2006#) AND T.ENROLLMENTSTA TUS='C'
GROUP BY I.LEARNINGACTIV ITYTITLE, I.LEARNINGACTIV ITYCODE, I.CODE, I.STARTDATE, I.ENDDATE, I.UNITS, I.ENROLLEDCOUNT
UNION ALL SELECT I.LEARNINGACTIV ITYTITLE, I.LEARNINGACTIV ITYCODE, I.CODE, I.STARTDATE, I.ENDDATE, I.UNITS, I.ENROLLEDCOUNT , COUNT (T.ENROLLMENTST ATUS) AS COMPLETED, COUNT ( T.ENROLLMENTSTA TUS) AS ENROLLED, COUNT (T.ENROLLMENTST ATUS) AS NO_SHOW
FROM LMS650_DRLAINST ANCEFULL AS I, LMS650_DRLATRAN SCRIPTFULL AS T
WHERE T.LEARNINGACTIV ITYID=I.LEARNIN GACTIVITYID AND T.INSTANCECODE= I.CODE AND I.LEARNINGACTIV ITYCODE LIKE 'ODE%' AND ((I.STARTDATE) Between #8/1/2006# And #8/31/2006#) AND T.ENROLLMENTSTA TUS='E'
GROUP BY I.LEARNINGACTIV ITYTITLE, I.LEARNINGACTIV ITYCODE, I.CODE, I.STARTDATE, I.ENDDATE, I.UNITS, I.ENROLLEDCOUNT
UNION ALL SELECT I.LEARNINGACTIV ITYTITLE, I.LEARNINGACTIV ITYCODE, I.CODE, I.STARTDATE, I.ENDDATE, I.UNITS, I.ENROLLEDCOUNT , COUNT (T.ENROLLMENTST ATUS) AS COMPLETED, COUNT ( T.ENROLLMENTSTA TUS) AS ENROLLED, COUNT (T.ENROLLMENTST ATUS) AS NO_SHOW
FROM LMS650_DRLAINST ANCEFULL AS I, LMS650_DRLATRAN SCRIPTFULL AS T
WHERE T.LEARNINGACTIV ITYID=I.LEARNIN GACTIVITYID AND T.INSTANCECODE= I.CODE AND I.LEARNINGACTIV ITYCODE LIKE 'ODE%' AND ((I.STARTDATE) Between #8/1/2006# And #8/31/2006#) AND T.ENROLLMENTSTA TUS='N'
GROUP BY I.LEARNINGACTIV ITYTITLE, I.LEARNINGACTIV ITYCODE, I.CODE, I.STARTDATE, I.ENDDATE, I.UNITS, I.ENROLLEDCOUNT
ORDER BY I.LEARNINGACTIV ITYCODE, I.CODE, I.LEARNINGACTIV ITYTITLE, I.STARTDATE DESC;
As I indicated, the query is working correctly and pulling correct data except for the count. I am getting the correct count for the first select statement in the completed column, but the count for the completed column also appears in the enrolled column and the skipped column. I read something about counting horizontally vs vertically and tried a number of things including the case function, but have not been successful. We are on Windows 2002 professional. The DB is an oracle DB. I apologize if I am not presenting my question clearly, but this is my first post to a forum. Thank you.
Comment