Counting Records in A Group

  • Thread starter Thread starter Robin
  • Start date Start date
R

Robin

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];
 
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 said:
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 said:
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!
Click to expand...
Click to expand...
 
I can only work with information you provide.

What field contains 'Problem'?

--
Build a little, test a little.


kenista said:
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 said:
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 said:
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!
Click to expand...
Click to expand...
Click to expand...
 
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 said:
I can only work with information you provide.

What field contains 'Problem'?

--
Build a little, test a little.


kenista said:
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 said:
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.


:

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!
Click to expand...
Click to expand...
Click to expand...
 
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.
Click to expand...

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!
 
Back
Top