Counting Records in A Group

I have a table that has one row per email sent in by a student, organized by
the class they attend. I would like to count the number of students that have
sent in emails per class. I can't seem to get my brain around this, and what
I get every time is either the number of emails per class or the number of
emails per student.

My Table: [Publish Table]
Columns: [Course Name] = name of class,[People ID]=identifier for the
student,[Email ID]=identifier for the email in a different table

I've tried this query - gives me a row per student and the number of emails
for the student. Tried putting in DISTINCT and DISTINCTROW to no avail.

SELECT [Course Name], Count([People ID]) as CountPeople
FROM [Publish Table]
GROUP BY [Course Name],[People ID]

Instead I'd like to know how many students sent emails.

Help!

Try this --
SELECT [People ID], [Course Name], Count([Email ID]) as Email_Count
FROM [Publish Table]
GROUP BY [People ID], [Course Name];

--
Build a little, test a little.


"Robin" wrote:

I have a table that has one row per email sent in by a student, organized by
the class they attend. I would like to count the number of students that have
sent in emails per class. I can't seem to get my brain around this, and what
I get every time is either the number of emails per class or the number of
emails per student.

My Table: [Publish Table]
Columns: [Course Name] = name of class,[People ID]=identifier for the
student,[Email ID]=identifier for the email in a different table

I've tried this query - gives me a row per student and the number of emails
for the student. Tried putting in DISTINCT and DISTINCTROW to no avail.

SELECT [Course Name], Count([People ID]) as CountPeople
FROM [Publish Table]
GROUP BY [Course Name],[People ID]

Instead I'd like to know how many students sent emails.

Help!

Try this:

SELECT [Course Name], COUNT(*) AS StudentCount
FROM (SELECT DISTINCT [Course Name], [People ID]
FROM [Publish Table])
GROUP BY [Course Name];

The subquery returns the distinct values of Course name and StudentID, i.e
one row per course per student. By grouping the result of the subquery by
Course Name and counting the rows the number of students who have sent in one
or more emails per course is returned.

If you save the query and then reopen it in SQL view you'll fund that Access
has given the subquery an arbitrary alias. You can change the alias to
something more intelligible if you wish.

Ken Sheridan
Stafford, England

Robin wrote:
I have a table that has one row per email sent in by a student, organized by
the class they attend. I would like to count the number of students that have
sent in emails per class. I can't seem to get my brain around this, and what
I get every time is either the number of emails per class or the number of
emails per student.

My Table: [Publish Table]
Columns: [Course Name] = name of class,[People ID]=identifier for the
student,[Email ID]=identifier for the email in a different table

I've tried this query - gives me a row per student and the number of emails
for the student. Tried putting in DISTINCT and DISTINCTROW to no avail.

SELECT [Course Name], Count([People ID]) as CountPeople
FROM [Publish Table]
GROUP BY [Course Name],[People ID]

Instead I'd like to know how many students sent emails.

Help!

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access/201001/1

This has helped me out, but is there a way to also count the fields that have
no entries?

For example:

Problem Number of responses
enter key doesn't work 2
can't find help 0
what is a database 5

"KARL DEWEY" wrote:

Try this --
SELECT [People ID], [Course Name], Count([Email ID]) as Email_Count
FROM [Publish Table]
GROUP BY [People ID], [Course Name];

--
Build a little, test a little.


"Robin" wrote:

I have a table that has one row per email sent in by a student, organized by
the class they attend. I would like to count the number of students that have
sent in emails per class. I can't seem to get my brain around this, and what
I get every time is either the number of emails per class or the number of
emails per student.

My Table: [Publish Table]
Columns: [Course Name] = name of class,[People ID]=identifier for the
student,[Email ID]=identifier for the email in a different table

I've tried this query - gives me a row per student and the number of emails
for the student. Tried putting in DISTINCT and DISTINCTROW to no avail.

SELECT [Course Name], Count([People ID]) as CountPeople
FROM [Publish Table]
GROUP BY [Course Name],[People ID]

Instead I'd like to know how many students sent emails.

Help!

I can only work with information you provide.

What field contains 'Problem'?

--
Build a little, test a little.


"kenista" wrote:

This has helped me out, but is there a way to also count the fields that have
no entries?

For example:

Problem Number of responses
enter key doesn't work 2
can't find help 0
what is a database 5

"KARL DEWEY" wrote:

Try this --
SELECT [People ID], [Course Name], Count([Email ID]) as Email_Count
FROM [Publish Table]
GROUP BY [People ID], [Course Name];

--
Build a little, test a little.


"Robin" wrote:

I have a table that has one row per email sent in by a student, organized by
the class they attend. I would like to count the number of students that have
sent in emails per class. I can't seem to get my brain around this, and what
I get every time is either the number of emails per class or the number of
emails per student.

My Table: [Publish Table]
Columns: [Course Name] = name of class,[People ID]=identifier for the
student,[Email ID]=identifier for the email in a different table

I've tried this query - gives me a row per student and the number of emails
for the student. Tried putting in DISTINCT and DISTINCTROW to no avail.

SELECT [Course Name], Count([People ID]) as CountPeople
FROM [Publish Table]
GROUP BY [Course Name],[People ID]

Instead I'd like to know how many students sent emails.

Help!

You'll need two tables, one for Problems the other for Responses, related on
Problem. Then use a LEFT OUTER JOIN in the query:

SELECT Problems.Problem,
COUNT(Responses.Problem) AS [Number of Responses]
FROM Problems LEFT JOIN Responses
ON Problems.Problem = Responses.Problem
GROUP BY Problems.Problem;

Ken Sheridan
Stafford, England

kenista wrote:
This has helped me out, but is there a way to also count the fields that have
no entries?

For example:

Problem Number of responses
enter key doesn't work 2
can't find help 0
what is a database 5

Try this --
SELECT [People ID], [Course Name], Count([Email ID]) as Email_Count
[quoted text clipped - 21 lines]

Help!

--
Message posted via http://www.accessmonster.com

My code is as follows:

SELECT Problem, Count(Problem) AS Problem_Count
FROM Queries_Table
GROUP BY Problem;

I want to be able to show (count) the fields that don't have any entries.

"KARL DEWEY" wrote:

I can only work with information you provide.

What field contains 'Problem'?

--
Build a little, test a little.


"kenista" wrote:

This has helped me out, but is there a way to also count the fields that have
no entries?

For example:

Problem Number of responses
enter key doesn't work 2
can't find help 0
what is a database 5

"KARL DEWEY" wrote:

Try this --
SELECT [People ID], [Course Name], Count([Email ID]) as Email_Count
FROM [Publish Table]
GROUP BY [People ID], [Course Name];

--
Build a little, test a little.


"Robin" wrote:

I have a table that has one row per email sent in by a student, organized by
the class they attend. I would like to count the number of students that have
sent in emails per class. I can't seem to get my brain around this, and what
I get every time is either the number of emails per class or the number of
emails per student.

My Table: [Publish Table]
Columns: [Course Name] = name of class,[People ID]=identifier for the
student,[Email ID]=identifier for the email in a different table

I've tried this query - gives me a row per student and the number of emails
for the student. Tried putting in DISTINCT and DISTINCTROW to no avail.

SELECT [Course Name], Count([People ID]) as CountPeople
FROM [Publish Table]
GROUP BY [Course Name],[People ID]

Instead I'd like to know how many students sent emails.

Help!

On Thu, 14 Jan 2010 16:51:01 -0800, kenista
wrote:

My code is as follows:

SELECT Problem, Count(Problem) AS Problem_Count
FROM Queries_Table
GROUP BY Problem;

I want to be able to show (count) the fields that don't have any entries.

You will need (and, hopefully, already have) another table containing all the
valid values of Problem. Let's call it Problems. A query

SELECT P.Problem, Count(*) AS Problem_Count
FROM Problem AS P LEFT JOIN Queries_Table AS Q
ON P.Problem = Q.Problem;

The ahem problem is that if there is no record in Queries_Table then Access
can't count what isn't there! I'm pretty sure there are no records where the
Problem is "Insufficient Energy in the Freem drives for liftoff"... but I
doubt that you want to see that with a 0 by it!
--

John W. Vinson [MVP]