total points then print those that are over a threshold

[Sue]

I created a database for a school that tracks infractions. Each time a
student uses their phone or violates a dress code, they get an infraction
point. I have created a query and report that sums the infraction points per
student. I now want to restrict my report to only show those students that
have more than 4 points, or more than 8 points, etc. If I enter the >3 in
the criteria in the query along with the sum, Access won't let me because it
hasn't yet summed the points. Help - where can I set the criteria?

 

[Ofer Cohen]

Can you post the SQL you tried?

The criteria should be under the sum field

Something like:

Select Sum([FieldName]), FieldName2 From TableName
Group By FieldName2
HAVING Sum([FieldName]) > 3

And not:
Select Sum([FieldName]), FieldName2 From TableName
Group By FieldName2 , FieldName
HAVING [FieldName] > 3


Good Luck
BS"D

 

[Sue]

I don't know if I mentioned it, but I'm using Access 2002.
Here's what I've entered:
(Select Sum( [IncidentPoints] ), [StuID] From IncidentDetail
Group By [StuID]
HAVING Sum( [IncidentPoints]) >3)

Access first requested that the subquery be enclosed in parentheses. I
added the parenthesis. After that, Access is displayed a message that at
most one record can be returned by the query (there should be multiple) and
will not run the query.
When I includ the table name with the field names, such as
[IncidentDetail!IncidentPoints], then the error message reads:

"You have written a subquery that can return more than one field without
using the EXISTS reserved word in the main query's FROM clause. Revise the
SELECT statement of the subquery to request only one field."

--
Sue K.

Can you post the SQL you tried?

The criteria should be under the sum field

Something like:

Select Sum([FieldName]), FieldName2 From TableName
Group By FieldName2
HAVING Sum([FieldName]) > 3

And not:
Select Sum([FieldName]), FieldName2 From TableName
Group By FieldName2 , FieldName
HAVING [FieldName] > 3


Good Luck
BS"D

I created a database for a school that tracks infractions. Each time a
student uses their phone or violates a dress code, they get an infraction
point. I have created a query and report that sums the infraction points per
student. I now want to restrict my report to only show those students that
have more than 4 points, or more than 8 points, etc. If I enter the >3 in
the criteria in the query along with the sum, Access won't let me because it
hasn't yet summed the points. Help - where can I set the criteria?

 

[Ofer Cohen]

My mistake in the example, need to define a new name to the SUM

Select Sum( [IncidentPoints] ) As SumOfPoint, [StuID]
From IncidentDetail
Group By [StuID]
HAVING Sum( [IncidentPoints]) >3
--
Good Luck
BS"D

I don't know if I mentioned it, but I'm using Access 2002.
Here's what I've entered:
(Select Sum( [IncidentPoints] ), [StuID] From IncidentDetail
Group By [StuID]
HAVING Sum( [IncidentPoints]) >3)

Access first requested that the subquery be enclosed in parentheses. I
added the parenthesis. After that, Access is displayed a message that at
most one record can be returned by the query (there should be multiple) and
will not run the query.
When I includ the table name with the field names, such as
[IncidentDetail!IncidentPoints], then the error message reads:

"You have written a subquery that can return more than one field without
using the EXISTS reserved word in the main query's FROM clause. Revise the
SELECT statement of the subquery to request only one field."

--
Sue K.

Can you post the SQL you tried?

The criteria should be under the sum field

Something like:

Select Sum([FieldName]), FieldName2 From TableName
Group By FieldName2
HAVING Sum([FieldName]) > 3

And not:
Select Sum([FieldName]), FieldName2 From TableName
Group By FieldName2 , FieldName
HAVING [FieldName] > 3


Good Luck
BS"D

I created a database for a school that tracks infractions. Each time a
student uses their phone or violates a dress code, they get an infraction
point. I have created a query and report that sums the infraction points per
student. I now want to restrict my report to only show those students that
have more than 4 points, or more than 8 points, etc. If I enter the >3 in
the criteria in the query along with the sum, Access won't let me because it
hasn't yet summed the points. Help - where can I set the criteria?

 

[Sue]

Thanks for your response. I will try it and let you know how it goes.
--
Sue K.

My mistake in the example, need to define a new name to the SUM

Select Sum( [IncidentPoints] ) As SumOfPoint, [StuID]
From IncidentDetail
Group By [StuID]
HAVING Sum( [IncidentPoints]) >3
--
Good Luck
BS"D

I don't know if I mentioned it, but I'm using Access 2002.
Here's what I've entered:
(Select Sum( [IncidentPoints] ), [StuID] From IncidentDetail
Group By [StuID]
HAVING Sum( [IncidentPoints]) >3)

Access first requested that the subquery be enclosed in parentheses. I
added the parenthesis. After that, Access is displayed a message that at
most one record can be returned by the query (there should be multiple) and
will not run the query.
When I includ the table name with the field names, such as
[IncidentDetail!IncidentPoints], then the error message reads:

"You have written a subquery that can return more than one field without
using the EXISTS reserved word in the main query's FROM clause. Revise the
SELECT statement of the subquery to request only one field."

--
Sue K.

Can you post the SQL you tried?

The criteria should be under the sum field

Something like:

Select Sum([FieldName]), FieldName2 From TableName
Group By FieldName2
HAVING Sum([FieldName]) > 3

And not:
Select Sum([FieldName]), FieldName2 From TableName
Group By FieldName2 , FieldName
HAVING [FieldName] > 3


Good Luck
BS"D


:

I created a database for a school that tracks infractions. Each time a
student uses their phone or violates a dress code, they get an infraction
point. I have created a query and report that sums the infraction points per
student. I now want to restrict my report to only show those students that
have more than 4 points, or more than 8 points, etc. If I enter the >3 in
the criteria in the query along with the sum, Access won't let me because it
hasn't yet summed the points. Help - where can I set the criteria?