Counting query

[Microsoft]

Hi,

I have 2 tables of data as follows:

table1: studentdata - this table stores the details of every student within
the school
fields: ID, surname, given names, year level

table2: incidentdetails - this table allows incidents to be recorded against
any student in table 1
fields: incidid (autonumber), ID, incidentdetails, incidentcode,
suspensiondate, suspendeddays, reviewed (yes/no field)

Briefly, for every incident, a student may be suspended or their incident
reviewed or both. If they are suspended, a value is entered in suspenddays
field. If they are reviewed then that field is ticked (yes selected).

The ID field joins the 2 tables

What I am having trouble with is designing a query that will return the
following information

ID, Surname, given names, count of number of suspensions, count of number of
reviews

This data is to be grouped so that students with more than one suspension do
not appear twice in the results but rather the number of reviews / incidents
they have acccrued are tallied against their name.

Sample desired output

ID, Surname, given names, count of number of incidents, count of number
of reviews
A2, ABBOTT, Mark , 1 , 2
A54, GRANT , Cheree , 2 , 0
A43, TOMS , John ,1 ,
0

This is the sql of my unsuccessful query.

SELECT incidentdetails.ID, StudentData.FamilyName, StudentData.GivenName,
StudentData.Gender, StudentData.Yearlevel, StudentData.HomeGroup,
Count(incidentdetails.ID) AS noofsuspensions, Count(incidentdetails.review)
AS CountOfReview
FROM StudentData INNER JOIN DetailsOfSuspensions ON StudentData.ID =
incidentdetails.ID
GROUP BY incidentdetails.ID, StudentData.FamilyName, StudentData.GivenName,
StudentData.Yearlevel;

Any help will be appreciated,

Thanks

Rocky

 

[Marshall Barton]

I have 2 tables of data as follows:

table1: studentdata - this table stores the details of every student within
the school
fields: ID, surname, given names, year level

table2: incidentdetails - this table allows incidents to be recorded against
any student in table 1
fields: incidid (autonumber), ID, incidentdetails, incidentcode,
suspensiondate, suspendeddays, reviewed (yes/no field)

Briefly, for every incident, a student may be suspended or their incident
reviewed or both. If they are suspended, a value is entered in suspenddays
field. If they are reviewed then that field is ticked (yes selected).

The ID field joins the 2 tables

What I am having trouble with is designing a query that will return the
following information

ID, Surname, given names, count of number of suspensions, count of number of
reviews

This data is to be grouped so that students with more than one suspension do
not appear twice in the results but rather the number of reviews / incidents
they have acccrued are tallied against their name.

Sample desired output

ID, Surname, given names, count of number of incidents, count of number
of reviews
A2, ABBOTT, Mark , 1 , 2
A54, GRANT , Cheree , 2 , 0
A43, TOMS , John ,1 ,
0

This is the sql of my unsuccessful query.

SELECT incidentdetails.ID, StudentData.FamilyName, StudentData.GivenName,
StudentData.Gender, StudentData.Yearlevel, StudentData.HomeGroup,
Count(incidentdetails.ID) AS noofsuspensions, Count(incidentdetails.review)
AS CountOfReview
FROM StudentData INNER JOIN DetailsOfSuspensions ON StudentData.ID =
incidentdetails.ID
GROUP BY incidentdetails.ID, StudentData.FamilyName, StudentData.GivenName,
StudentData.Yearlevel;

What about your query is not working?

There is a discrepancy in your query in that you use the
table DetailsOfSuspensions in the FROM clause, but you refer
to incidentdetails most everywhere else.

I suspect that the issue is that the count(s?) are counting
all the records regardless of whether a review was held or
not. This would be caused by the review field having a
value of True or False and the fact that all the aggregate
functions operate on any value except Null values. In this
case you can get the count of reviews by using any of the
following expressions (depending on which one you understand
most clearly):

Count(IIf(incidentdetails.review, 1, Null))
Sum(IIf(incidentdetails.review, 1, 0))
Abs(Sum(incidentdetails.review))

For the Suspensions count, you are counting the number of
incidents regardless of the number in suspendeddays. I am
not entirely sure what you want here, but you I think you
can sount the number of suspensions using:
Count(suspensiondate)

or the number of days suspended:
Sum(suspendeddays)