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.
1 4522 PEB 1,418
Recognized Expert Top Contributor
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!
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: David Logan |
last post by:
We need an additional function in the String class. We need the ability
to suppress empty fields, so that we can more effectively parse. Right
now, multiple whitespace characters create multiple empty strings in the
resulting string array.
|
by: Dnna |
last post by:
I have a table which is bound to an Internet Explorer XML
data island. I'm using ASP.NET's client-side validators
for an input field in the table. The problem is that if
the input fields are in a table that is bound to the
island (such that the rows are dynamically generated by IE
from the XML data island), the validators do not work. If
the table is not bound, the validators do work, but then I
don't have the table iterating through...
|
by: Brad |
last post by:
I have written some code to manipulate data/records in a MASTER (order
header) and DETAIL (order details) tables.
What I have written is too extensive to post but essentially trying to:
1. Assign to a datarow (dr1) the first record of the MASTER table
2. Assign to another datarow (dr2) the second record of the MASTER table
3. If dr1.field1 = dr2.field1, then proceed, otherwise do stop
4. Assign to a third datarow (dr3) the first record...
|
by: shapper |
last post by:
Hello,
I have the following code:
1 ' Code that creates the connection, parameters, etc
2 ' ...
3
4 ' Execute the command
5
6 ' ???? THE CODE I NEED TO KNOW
|
by: AMDRIT |
last post by:
Occasionally I try my hand at a simple data storage engine. Today I ran
across an article on the web
http://msdn2.microsoft.com/en-us/library/aa289151(vs.71).aspx, and it got me
thinking again. There doesn't seem to be a definative source, at least on
in layman terms, on this subject.
Here is what I believe to know so far. (As if I was to model after SQL
Server).
| |
by: Cintury |
last post by:
Hi all,
I've developed a mobile application for windows mobile 5.0 that has been in use for a while (1 year and a couple of months). It was developed in visual studios 2005 with a back-end sql server mobile ce database. Until recently I was synching everything thru a com port serial cable. The devices would connect to the computer thru activesync and are able to acquire an internet connection. The sync for the program occurs thru a website...
|
by: ADezii |
last post by:
One question which pops up frequently here at TheScripts is: 'How do I retrieve data from a Recordset once I've created it?' One very efficient, and not that often used approach, is the GetRows() Method of the Recordset Object. This Method varies slightly from DAO to ADO, so for purposes of this discussion, we'll be talking about DAO Recordsets. The ADO approach will be addressed in the following Tip. We'll be using a Query, consisting of 5...
|
by: ADezii |
last post by:
Last Tip, we demonstrated the technique for retrieving data from a DAO Recordset, and placing it into a 2-dimensional Array using the GetRows() Method. This week, we will cover the same exact Method (GetRows()), but only as it applies to an ADO Recordset. Although there are similarities in the 2 methodologies, the ADO Method offers 2 more Optional Arguments, is a little more complex, and of course, the syntax is different in creating the...
|
by: wed1 |
last post by:
Hi all
I was asked from the company i support their intranet with asp to create a vb prog in order to retrive magazine circulations from their oracle server and update the number of circulations if needed to be corrected.
After searching a lot the web i have come up with the following code, apart from the date selection criteria, that works in retrieving and displaying the data as i was asked to do:
Dim SrText As String = ""
Dim...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |