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

How to split one data field into 3 and count number of rows in each?

P: 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.
Sep 18 '06 #1
Share this Question
Share on Google+
1 Reply


PEB
Expert 100+
P: 1,418
PEB
Hi,

it seems you want to use the count on different groups in one query doesn't it?

For every different grouping you need a different query!
Sep 19 '06 #2

Post your reply

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