A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

SELECT Statement Question



 
 
Thread Tools Display Modes
  #1  
Old January 15th, 2010, 09:04 PM posted to microsoft.public.access.queries
Robin
external usenet poster
 
Posts: 481
Default 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  
Old January 15th, 2010, 09:13 PM posted to microsoft.public.access.queries
Clifford Bass via AccessMonster.com
external usenet poster
 
Posts: 150
Default 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  
Old January 15th, 2010, 09:19 PM posted to microsoft.public.access.queries
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default 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  
Old January 15th, 2010, 09:29 PM posted to microsoft.public.access.queries
John Spencer
external usenet poster
 
Posts: 7,815
Default 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  
Old January 15th, 2010, 09:43 PM posted to microsoft.public.access.queries
Clifford Bass via AccessMonster.com
external usenet poster
 
Posts: 150
Default 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  
Old January 16th, 2010, 06:44 PM posted to microsoft.public.access.queries
Robin
external usenet poster
 
Posts: 481
Default 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  
Old January 16th, 2010, 06:45 PM posted to microsoft.public.access.queries
Robin
external usenet poster
 
Posts: 481
Default 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  
Old January 16th, 2010, 06:46 PM posted to microsoft.public.access.queries
Robin
external usenet poster
 
Posts: 481
Default 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  
Old January 18th, 2010, 05:14 AM posted to microsoft.public.access.queries
Clifford Bass via AccessMonster.com
external usenet poster
 
Posts: 150
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:48 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.