Complex DCount in a Student "Summary" Form

[6afraidbecause789]

Hi - In a continuous “summary” form, data on student misbehavior
incidents are currently summarized in the following columns:

StudentName ContactInfo IncidentDetails Staff
IncidentDateTime and then in a DCount field that has this control
source:

=DCount("StudentID","Incidents","StudentID=" & Forms!
frmIncidentSummary!StudentID & " and datediff(""d"",
IncidentDateTime,date())=0")

This DCount works—if a new incident is added for a student, the DCount
auto updates in the form and tallies the number of incidents per day
for all students that have had incidents.

NOW, users want to break this down a step further and tally the
“types” of incidents that the students have per day. Currently, there
are 5 incident types:

IncTypeID 1 2 3 4 5
IncType --- Hallway SBooth SRoom On-Ground Off-
Ground
IncAbbreviation HAL SB SR ON OFF

I will make a column on the form for each type of incident.

For instance, if a student has had 2 HAL incidents and just one SB and
SR incident in a day, the student’s name will occupy 4 rows—the DCount
should put a 2 in the HAL column and a 1 in the SB and SR columns.

How can this be done?

If you wonder, "Gosh, why might there be so many student misbehavior
incidents each day?" - the answer is that this dbase will be used by a
school servicing special needs kids. Thanks a bunch.

 

[scubadiver]

if you are listing every incident for each child why not just do a group
total query that counts up the numbers of incidents and then use that as the
basis for a cross tab query.

What are you using the dcount for?