counts/sums in query/crosstab or report?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've been struggling with this although I'm sure it's fairly simple. I need
to have a report that is broken down in 4 levels (District, Subdistrict,
Area, Field) and inside each level there are some fields (regarding
Inspections) which I need to count or sum but I need to do it for each level.

ie. For each Field # of Enf_categories = "Minor", # of Enf_categories =
"Major", # of Enf_categories = "Satisfactory".....
I would then need to do the same for each Area, Subdistrict and District.

I have built a report from a Select Query (that includes 3 tables) and
currently it does list each record and below that it counts the number of
inspections for each group in seperate footers but I cannot figure out how to
count the details of the inspections. I'm not even sure if it's possible in
the query I have, or if I need to do a crosstab query (no experience) or if I
can put the calculations directly into my report. Any direction is much
appreciated!
 
Try the Sorting and Grouping in the Report. Click on Design and then Sorting
and Grouping.
 
Thanks for the response - I actually have the report sorted and grouped the
way I want it. What I'm having trouble figuring out is how to put in the
calculation to count the records in the details at the appropriate level.
For example

District: Central
Subdistrict: South Central
Area: Slave Lake
Field: Mitsue
Details of Inspections - one line per record

I need to report on the Inspection details at every level so, there may have
been 20 inspections in Central for the quarter but only 5 of them happened in
Mitsue and the other 15 were in other "Fields". My report does the breakdown
and counts the 5 records (inspections) in Mitsue, say 8 in Slave Lake 12 in
South Central and 20 in Central.

The details of the Inspections are listed under the "Field" but they vary.
For example the Category could be "Major", "Minor", "Serious" or
"Satisfactory". So what I need is a count of how many in the Mitsue field
belonged to each category (ie. 1 Minor, 1 Major and 3 Satisfactory).

I then need to count how many in the Slave Lake area fell into each
category......and likewise for Subdistrict and District.

I have tried fooling around with calculations in the report itself but end
up with numbers that are not correct. I am wondering if it is even possible
to do this in the report?
 
Back
Top