0

I currently have this in MS Access:

SELECT ClassID, Count(Component) AS ActiveDuty FROM tblStudents WHERE Component = "Active_Duty" GROUP BY classID;

It gives me the correct answer which looks like this: ClassID ActiveDuty 006-14 14 007-14 12 008-14 8

But if I want it to look like this what do I need to do?

ClassID ActiveDuty Reserve National Guard 006-14 14 5 6 007-14 12 9 8 008-14 8 7 18

I tried using subqueries like this:

SELECT ClassID, (SELECT COUNT(Component) FROM tblStudents WHERE Component = "ActiveDuty") AS Active_Duty,(SELECT COUNT(Component) FROM tblStudents WHERE Component = "Reserve") AS ArmyReserve, (SELECT COUNT(Component) FROM tblStudents WHERE Component = "National_Guard") AS NationalGuard FROM tblStudents WHERE Component = "Active_Duty" GROUP BY ClassID;

But this is the result I get:

ClassID ActiveDuty Reserve National Guard 006-14 34 37 29 007-14 34 37 29 008-14 34 37 29

billmann
  • 57
  • 8

2 Answers2

0

You can use MS Access’ wizard to help you make a Crosstab Query for this. It will generate something like this:

TRANSFORM Count(tblStudents.[Component]) AS CountOfComponent
SELECT tblStudents.[ClassID], Count(tblStudents.[Component]) AS [Total Of Component]
FROM tblStudents
GROUP BY tblStudents.[ClassID]
PIVOT tblStudents.[Component];
jforbes
  • 92
  • 1
  • WORKED LIKE MAGIC!! But if there is a way to do it in SQL I would love to know for future reference. – billmann Aug 20 '14 at 01:35
  • What if I want to include the count of Gender as well? So the total number of Males and Females in each Class? – billmann Aug 20 '14 at 02:07
  • This is article gives a pretty good understanding of the difference between MS Access' and TSQL's PIVOT: [link]http://stackoverflow.com/questions/13953134/convert-access-transform-pivot-query-to-sql-server – jforbes Aug 20 '14 at 12:32
0

Adding Gender could be a little more complicated depending on how you want to the table to look. If you want three dimensional data, someone else is going to have to help you. =) But if you don’t mind a 2D solution:

TRANSFORM Count(temp.[ComponentGender]) AS CountOfComponent
SELECT temp.[ClassID], Count(temp.[ComponentGender]) AS [Total Of ComponentGender]
FROM (SELECT ClassID, Component & "_" & Gender as ComponentGender FROM tblStudents) AS temp
GROUP BY temp.[ClassID]
PIVOT temp.[ComponentGender];
jforbes
  • 92
  • 1