10
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.LEARNINGACTIVITYTITLE, I.LEARNINGACTIVITYCODE, I.CODE, I.STARTDATE, I.ENDDATE, I.UNITS, I.ENROLLEDCOUNT, COUNT (T.ENROLLMENTSTATUS) AS COMPLETED, COUNT ( T.ENROLLMENTSTATUS) AS ENROLLED, COUNT (T.ENROLLMENTSTATUS) AS SKIPPED
FROM LMS650_DRLAINSTANCEFULL AS I, LMS650_DRLATRANSCRIPTFULL AS T
WHERE T.LEARNINGACTIVITYID=I.LEARNINGACTIVITYID AND T.INSTANCECODE= I.CODE AND I.LEARNINGACTIVITYCODE LIKE 'ODE%' AND ((I.STARTDATE) Between #8/1/2006# And #8/31/2006#) AND T.ENROLLMENTSTATUS='C'
GROUP BY I.LEARNINGACTIVITYTITLE, I.LEARNINGACTIVITYCODE, I.CODE, I.STARTDATE, I.ENDDATE, I.UNITS, I.ENROLLEDCOUNT
UNION ALL SELECT I.LEARNINGACTIVITYTITLE, I.LEARNINGACTIVITYCODE, I.CODE, I.STARTDATE, I.ENDDATE, I.UNITS, I.ENROLLEDCOUNT, COUNT (T.ENROLLMENTSTATUS) AS COMPLETED, COUNT ( T.ENROLLMENTSTATUS) AS ENROLLED, COUNT (T.ENROLLMENTSTATUS) AS NO_SHOW
FROM LMS650_DRLAINSTANCEFULL AS I, LMS650_DRLATRANSCRIPTFULL AS T
WHERE T.LEARNINGACTIVITYID=I.LEARNINGACTIVITYID AND T.INSTANCECODE= I.CODE AND I.LEARNINGACTIVITYCODE LIKE 'ODE%' AND ((I.STARTDATE) Between #8/1/2006# And #8/31/2006#) AND T.ENROLLMENTSTATUS='E'
GROUP BY I.LEARNINGACTIVITYTITLE, I.LEARNINGACTIVITYCODE, I.CODE, I.STARTDATE, I.ENDDATE, I.UNITS, I.ENROLLEDCOUNT
UNION ALL SELECT I.LEARNINGACTIVITYTITLE, I.LEARNINGACTIVITYCODE, I.CODE, I.STARTDATE, I.ENDDATE, I.UNITS, I.ENROLLEDCOUNT, COUNT (T.ENROLLMENTSTATUS) AS COMPLETED, COUNT ( T.ENROLLMENTSTATUS) AS ENROLLED, COUNT (T.ENROLLMENTSTATUS) AS NO_SHOW
FROM LMS650_DRLAINSTANCEFULL AS I, LMS650_DRLATRANSCRIPTFULL AS T
WHERE T.LEARNINGACTIVITYID=I.LEARNINGACTIVITYID AND T.INSTANCECODE= I.CODE AND I.LEARNINGACTIVITYCODE LIKE 'ODE%' AND ((I.STARTDATE) Between #8/1/2006# And #8/31/2006#) AND T.ENROLLMENTSTATUS='N'
GROUP BY I.LEARNINGACTIVITYTITLE, I.LEARNINGACTIVITYCODE, I.CODE, I.STARTDATE, I.ENDDATE, I.UNITS, I.ENROLLEDCOUNT
ORDER BY I.LEARNINGACTIVITYCODE, I.CODE, I.LEARNINGACTIVITYTITLE, 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.
