How to 'Count' Multiple fields??

  • Thread starter Thread starter Ben Johnson
  • Start date Start date
B

Ben Johnson

My database needs to run a report that will give the total number of
each specific type of inspection given a user-defined date range. I
have about 15 different inspection types. The inspection type is
selected by the user when they book the inspection by placing a tick in
the relevant check box.

I'd want the output to look like this:

From [Start Date] to [End Date]:

Inspection1.....................12
Inspection2.....................3
Inpsection3.....................0
etc.

Using "Totals" I'm not getting any differentiation between inspection
types by using the 'Count' keyword - that is, all I get is the total
number of inspections for the given date range in each field, like this:

Inspection1......................25
Inspection2......................25
Inspection3......................25
etc.

How do I get access to break this total down into it's component parts?

Many thanks,
Ben Johnson

Please ignore the address listed with the post. My email address is:
b<xremovex>johnson (at) netspace dot net dot au
 
Do you have one field per inspection? If so, is there any chance that you
could change it to a more normalized table structure?
 
Unfortunately no. One inspection will often encompass two (or more) fields.

Cheers,
Ben Johnson

Please ignore the address listed with the post. My email address is:
b<xremovex>johnson (at) netspace dot net dot au
 
Instead of using count, use Sum - This should work since a checkbox (yes/no)
field has a value of -1 when checked and 0 when not checked. This will return a
negative, so you can multiply that by -1, use the Abs function, or choose to
display numbers with no negative sign.

Field: Inspection1Count: Abs(Sum(Inspection1))
Total: Expression
 
Back
Top