SELECT Statement Question

R

Robin

Table: [Publish Table]
Description: One row per email sent in by a student. Emails are feedback on
the course they are taking.

Columns:
[Course Name] = class they are taking
[People ID] = student identifier
[Comment] = comment about the class that day
[Date] = date/time of the email

Goal: I'd like to count the number of students that have submitted an email
by the course name. Everything I've tried returns to me the number of records
(=emails) by course.

Query:
SELECT [Publish Table].[Course Name], Count([Publish Table].[People ID]) AS
CountPeople
FROM [Publish Table]
GROUP BY [Publish Table].[Course Name], [Publish Table].[People ID];

Example Data (there is also a date and other data per row)
Course Name Student ID Comment
Course 01 01 I loved the class today.
Course 01 01 Tests were too hard today.
Course 01 02 This class is boring.
Course 01 03 I had to take this class, I don't like it.
Course 01 04 I realized I actually do like this class.
Course 02 02 This is the best class I've ever had.
Course 02 02 I don't understand the material.
Course 02 03 I love this class.

What I want to get back from my query:
Course 01 3 (i.e. three people in course one sent in one or more comment)
Course 02 2

What I get back from my query:
Course 01 2 (number of emails from student 01)
Course 01 1 (number of emails from student 02)
Course 01 1 (number of emails from student 03)
Course 02 2 (number of emails from student 02)
Course 02 1 (number of emails from student 03)

What am I doing wrong?
 
K

KARL DEWEY

You keep changing things. Is the field named People ID or is it Student ID?

SELECT [Publish Table].[Course Name], [Student ID], Count([Publish
Table].[Comment]) AS Count_e_mail
FROM [Publish Table]
GROUP BY [Publish Table].[Course Name], [Publish Table].[Student ID];
 
J

John Spencer

Query one - saved as TheSavedQuery (in this example)
SELECT DISTINCT [Course Name], [people ID]
FROM [Publish Table]

Query two uses query one to get the unique count
SELECT [Course Name], Count([People Id] as RespondentCount
FROM [TheSavedQuery]
GROUP BY [Course Name]

If you followed the naming guidelines and did not have spaces (or other
non-letter, non-number characters) in your table and field names you could do
this with one query with a sub-query.

SELECT DistinctPersons.CourseName, Count(PeopleId as RespondentCount
FROM (SELECT DISTINCT CourseName, PeopleID
FROM PublishTable) as DistinctPersons
GROUP BY CourseName

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
R

Robin

So sorry, the table field is called People ID, I used Student ID in my
example data because that is how I think of the data. My apologies.

KARL DEWEY said:
You keep changing things. Is the field named People ID or is it Student ID?

SELECT [Publish Table].[Course Name], [Student ID], Count([Publish
Table].[Comment]) AS Count_e_mail
FROM [Publish Table]
GROUP BY [Publish Table].[Course Name], [Publish Table].[Student ID];


--
Build a little, test a little.


Robin said:
Table: [Publish Table]
Description: One row per email sent in by a student. Emails are feedback on
the course they are taking.

Columns:
[Course Name] = class they are taking
[People ID] = student identifier
[Comment] = comment about the class that day
[Date] = date/time of the email

Goal: I'd like to count the number of students that have submitted an email
by the course name. Everything I've tried returns to me the number of records
(=emails) by course.

Query:
SELECT [Publish Table].[Course Name], Count([Publish Table].[People ID]) AS
CountPeople
FROM [Publish Table]
GROUP BY [Publish Table].[Course Name], [Publish Table].[People ID];

Example Data (there is also a date and other data per row)
Course Name Student ID Comment
Course 01 01 I loved the class today.
Course 01 01 Tests were too hard today.
Course 01 02 This class is boring.
Course 01 03 I had to take this class, I don't like it.
Course 01 04 I realized I actually do like this class.
Course 02 02 This is the best class I've ever had.
Course 02 02 I don't understand the material.
Course 02 03 I love this class.

What I want to get back from my query:
Course 01 3 (i.e. three people in course one sent in one or more comment)
Course 02 2

What I get back from my query:
Course 01 2 (number of emails from student 01)
Course 01 1 (number of emails from student 02)
Course 01 1 (number of emails from student 03)
Course 02 2 (number of emails from student 02)
Course 02 1 (number of emails from student 03)

What am I doing wrong?
Click to expand...
Click to expand...
 
R

Robin

Thank you so much, I did not realize I could use a query in the FROM
statement! Thank you!

Clifford Bass via AccessMonster.com said:
Hi Robin,

Sorry, did not read that correctly. Try:

Query1:
select distinct [Course Name], [People ID]
from [Publish Table];

Query2:
select [Course Name], Count(*) as Count_of_Students_Who_Commented
from Query1
group by [Course Name];

Clifford Bass

Clifford said:
Hi Robin,

Simply remove the People ID grouping:

...GROUP BY [Publish Table].[Course Name];

Clifford Bass
Click to expand...

--
Message posted via AccessMonster.com


.
Click to expand...
 
R

Robin

Thank you so much, I did not realize I could use a query in the FROM
statement. Thank you!

John Spencer said:
Query one - saved as TheSavedQuery (in this example)
SELECT DISTINCT [Course Name], [people ID]
FROM [Publish Table]

Query two uses query one to get the unique count
SELECT [Course Name], Count([People Id] as RespondentCount
FROM [TheSavedQuery]
GROUP BY [Course Name]

If you followed the naming guidelines and did not have spaces (or other
non-letter, non-number characters) in your table and field names you could do
this with one query with a sub-query.

SELECT DistinctPersons.CourseName, Count(PeopleId as RespondentCount
FROM (SELECT DISTINCT CourseName, PeopleID
FROM PublishTable) as DistinctPersons
GROUP BY CourseName

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Table: [Publish Table]
Description: One row per email sent in by a student. Emails are feedback on
the course they are taking.

Columns:
[Course Name] = class they are taking
[People ID] = student identifier
[Comment] = comment about the class that day
[Date] = date/time of the email

Goal: I'd like to count the number of students that have submitted an email
by the course name. Everything I've tried returns to me the number of records
(=emails) by course.

Query:
SELECT [Publish Table].[Course Name], Count([Publish Table].[People ID]) AS
CountPeople
FROM [Publish Table]
GROUP BY [Publish Table].[Course Name], [Publish Table].[People ID];

Example Data (there is also a date and other data per row)
Course Name Student ID Comment
Course 01 01 I loved the class today.
Course 01 01 Tests were too hard today.
Course 01 02 This class is boring.
Course 01 03 I had to take this class, I don't like it.
Course 01 04 I realized I actually do like this class.
Course 02 02 This is the best class I've ever had.
Course 02 02 I don't understand the material.
Course 02 03 I love this class.

What I want to get back from my query:
Course 01 3 (i.e. three people in course one sent in one or more comment)
Course 02 2

What I get back from my query:
Course 01 2 (number of emails from student 01)
Course 01 1 (number of emails from student 02)
Course 01 1 (number of emails from student 03)
Course 02 2 (number of emails from student 02)
Course 02 1 (number of emails from student 03)

What am I doing wrong?
Click to expand...
.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Access Running Balance in Access 1
Counting Records in A Group 5
Running Balance (Debit and Credit) in Access 2007 4
sql statement 1
Receiving valid Multicast address failed on mini port driver 0
Crosstab query question 2
What Kind Of Query Can I use? 6
Missing records on a CrossTab 1

Top