Report - field for total number of checkboxes that have a yes

[fredk]

I have a database that lists exception requests. One of the fields is an "approved" checkbox - I am running a report that includes each person, the number of exceptions they have requested and the number that was approved.

I am having a problem getting the number of approved. I set up a query to pull the fields - then queried that query to get the total requests.... Just can't figure out how to get a total of approved. The desired report would read:

Name Exception requests Exceptions approved

Joe 25 10

Any help would be greatly appreciated! Thanks!

 

[sko]

Are you reporting any details or just the total requests as you have posted?

If it's summary only I'd use a crosstab query.

Create a crosstab query using the wizard.
The row headings will be your name field, the column headings will be your checkbox field, the value you want calculated will be the request and the function will be count. Your results should look something like this:

Name TotalRequests -1 0

Bob 10 6 4


In VB a true value (a check box that's checked) is given a value of -1. False (unchecked) is given a value of 0. So in the example Bob has 6 approved requests. If you would rather, you can change the headings to True/False or Yes/No by opening the query properties window and setting the column headings to "Yes", "No"

Create a report using the report wizard. Base it on your crosstab query and make the report "tabular". Open the report in design view, change the "-1" label to "Approved" and hide the "unapproved" field and label.

HTH

 

[davesmith]

Why make it harder, in your report have you 3 columns

Username
Requests
Approved

Group by user name, if you want your text to be on one line
i.e, name 3 2
then leave the group by section empty, reduce the height to 0 and all your records will look in line with no gaps.

Field1 = username
field2: Request = count(username)
field3: approved = countif(approved=-1)

i can't quite remember the syntax of the code, but it works, looke the commands up in help, it will help you.
Cheers and Stuff

DaveSmith

 

[JoeMiller]

Requested textbox ControlSource:
=Count([Exception])

Approved textbox ControlSource:
=Abs(Sum([Exception]))

HTH Joe Miller
joe.miller@flotech.net

 

[fredk]

Got it - As always, thanks so much for the help!