Avatar of xeb
xeb

asked on 

Query Help

Hi:

I have these two queries that work fine...

SELECT Students.sProgram, Count(Students.sStudent) AS CountOfsStudent
FROM Students INNER JOIN Data ON Students.sStudent = Data.dStudent
GROUP BY Students.sProgram;

SELECT Students.sProgram, Count(Students.sProgram) AS CountOfProgram
FROM Students
GROUP BY Students.sProgram;

I've been trying for an hour to get them into one query but I can't seem to make it work.

Thanks,

xeb
Microsoft AccessSQL

Avatar of undefined
Last Comment
xeb
Avatar of fanopoe
fanopoe
Flag of United States of America image

did you try this?

SELECT Students.sProgram, Count(Students.sProgram) AS CountOfProgram, Count(Students.sStudent) AS CountOfsStudent
FROM Students INNER JOIN Data ON Students.sStudent = Data.dStudent
GROUP BY Students.sProgram;
Avatar of xeb
xeb

ASKER

That does not work. It returns "sProgram" but it returns Count(Students.sStudent) AS CountOfsStudent
data twice. I don't get Count(Students.sProgram) AS CountOfProgram.
Avatar of fanopoe
fanopoe
Flag of United States of America image

I don't think I'm understanding your setup. Why are you joining on Data here?
SELECT Students.sProgram, Count(Students.sStudent) AS CountOfsStudent
FROM Students INNER JOIN Data ON Students.sStudent = Data.dStudent
GROUP BY Students.sProgram;

it is not in the results or the group by.
What is the nature of what you want to show?
It seems that your first one shows Program + student count, e.g.
band 123
football 45
etc...

and the second one just counts the programs

am I on the right track? can you clarify a little bit?
ASKER CERTIFIED SOLUTION
Avatar of woodan1
woodan1

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of woodan1
woodan1

Why not:
SELECT Students.sProgram, Count(Students.sProgram) AS CountOfProgram, Count(Students.sStudent) AS CountOfsStudent
FROM Students
GROUP BY Students.sProgram;
Avatar of woodan1
woodan1

Sorry fanopoe, didn't see your response prior to mine.
Avatar of xeb
xeb

ASKER

Thanks for answering.

fanopoe - Yes, that is correct.

woodan1 - Your first one works but I don't really understand it. Your second one returns duplicate data the reverse of the fanopoe suggestion.
Avatar of fanopoe
fanopoe
Flag of United States of America image

then if a result of the first one could be band 123, the count of the programs would be 1, because you have already grouped on that. So how do you envision the count of programs you are getting in your second query relating to band 123?
Avatar of woodan1
woodan1

The first one is what is called a nested sql statement.  More or less just takes the two queries and combines them matching on sProgram.  Still bothers me that the second one doesn't do what we intended.

I don't see any need to use the Data table...To make the nested one smaller:
SELECT T1.sProgram, CountOfsStudent, CountOfProgram
FROM (
SELECT Students.sProgram, Count(Students.sStudent) AS CountOfsStudent
FROM Students
GROUP BY Students.sProgram
) AS T1 INNER JOIN
(
SELECT Students.sProgram, Count(Students.sProgram) AS CountOfProgram
FROM Students
GROUP BY Students.sProgram
) AS T2 ON T1.sProgram=T2.sProgram
Avatar of xeb
xeb

ASKER

The data is from two tables...Students and Data.

The Students table has the sStudent and sProgram field. I need to know how many students are in each program.

The Data table has dStudent and a record each time they have activity and I need to know how many activities belong to each program.

Then I want that side-by-side in a query.

Again, this works fine...

SELECT T1.sProgram, CountOfsStudent, CountOfProgram
FROM (
SELECT Students.sProgram, Count(Students.sStudent) AS CountOfsStudent
FROM Students INNER JOIN Data ON Students.sStudent = Data.dStudent
GROUP BY Students.sProgram
) AS T1 INNER JOIN
(
SELECT Students.sProgram, Count(Students.sProgram) AS CountOfProgram
FROM Students
GROUP BY Students.sProgram
) AS T2 ON T1.sProgram=T2.sProgram

So if it's okay I guess I'll just use it.

Thanks,

xeb
Microsoft Access
Microsoft Access

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

226K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo