If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
SELECT Statement Question
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? |
#2
|
|||
|
|||
SELECT Statement Question
Hi Robin,
Simply remove the People ID grouping: ...GROUP BY [Publish Table].[Course Name]; Clifford Bass Robin wrote: 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? -- Message posted via http://www.accessmonster.com |
#3
|
|||
|
|||
SELECT Statement Question
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" wrote: 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? |
#4
|
|||
|
|||
SELECT Statement Question
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 Robin wrote: 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? |
#5
|
|||
|
|||
SELECT Statement Question
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 Bass wrote: Hi Robin, Simply remove the People ID grouping: ...GROUP BY [Publish Table].[Course Name]; Clifford Bass -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/201001/1 |
#6
|
|||
|
|||
SELECT Statement Question
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" wrote: 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" wrote: 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? |
#7
|
|||
|
|||
SELECT Statement Question
Thank you so much, I did not realize I could use a query in the FROM
statement! Thank you! "Clifford Bass via AccessMonster.com" wrote: 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 Bass wrote: Hi Robin, Simply remove the People ID grouping: ...GROUP BY [Publish Table].[Course Name]; Clifford Bass -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/201001/1 . |
#8
|
|||
|
|||
SELECT Statement Question
Thank you so much, I did not realize I could use a query in the FROM
statement. Thank you! "John Spencer" wrote: 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 Robin wrote: 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? . |
#9
|
|||
|
|||
SELECT Statement Question
Hi Robin,
You are welcome. Think of it this way. A query returns data as a table so it would make sense that it also could be used as a source "table" in another query. Just an FYI: Access's saved queries are the equivalent of other database's views. Clifford Bass Robin wrote: Thank you so much, I did not realize I could use a query in the FROM statement! Thank you! -- Message posted via http://www.accessmonster.com |
Thread Tools | |
Display Modes | |
|
|