Query of checkboxes.

  • Thread starter Thread starter Robert F.
  • Start date Start date
R

Robert F.

I’m building a database to track my golf statistics. I have a table with the
following records:

Name
Date
Course
Hole
Tees
Fairway in Reg (checkbox YES/NO)
Green in Reg (checkbox YES/NO)
Strokes
Number of Putts

I’m trying to run a query that gives me the percentage of Fairways (and
Greens) in regulation. Example, if I checked 9 out of 18 of the “Fairway in
Reg†checkboxes the query would return 50%. I cannot figure out how to run a
calculation on YES/NO information. Any ideas?
 
Use a Totals query (Total button on toolbar, in query design.)

It can give you the average of the field.
Negate the result: it yields -50%, since True is -1 and False is 0.
 
I’m building a database to track my golf statistics. I have a table with the
following records:

Name
Date
Course
Hole
Tees
Fairway in Reg (checkbox YES/NO)
Green in Reg (checkbox YES/NO)
Strokes
Number of Putts

I’m trying to run a query that gives me the percentage of Fairways (and
Greens) in regulation. Example, if I checked 9 out of 18 of the “Fairway in
Reg” checkboxes the query would return 50%. I cannot figure out how to run a
calculation on YES/NO information. Any ideas?
Click to expand...

A Yes/No field is actually stored as an integer; True is -1, False is 0.

So a Totals query could have a calculated field:

GreenPct: -Sum([Green In Reg]) / Count(*)
 
Thank you all for the info and taking the time to help me with this issue. I
am having trouble setting up the totals query. I am pretty good at Excel but
very new to Access and still trying to learn my way around. I’m also using
Access 2002 (don't know if that matters in how to set this up. When I try to
use the calculation posted below I get various errors. I can set up the query
to count total checkboxes; Yes checkboxes; or No checkboxes. What I can't
figure out is how to divide the Yes boxes by the Total boxes. Can you please
explain how to setup the Totals query correctly using the expression you
posted below: GreenPct: -Sum([Green In Reg]) / Count(*)

Thanks again for your help!

--
Respectfully,
Robert F.


John W. Vinson said:
I’m building a database to track my golf statistics. I have a table with the
following records:

Name
Date
Course
Hole
Tees
Fairway in Reg (checkbox YES/NO)
Green in Reg (checkbox YES/NO)
Strokes
Number of Putts

I’m trying to run a query that gives me the percentage of Fairways (and
Greens) in regulation. Example, if I checked 9 out of 18 of the “Fairway in
Reg†checkboxes the query would return 50%. I cannot figure out how to run a
calculation on YES/NO information. Any ideas?
Click to expand...

A Yes/No field is actually stored as an integer; True is -1, False is 0.

So a Totals query could have a calculated field:

GreenPct: -Sum([Green In Reg]) / Count(*)
Click to expand...
 
Can you please
explain how to setup the Totals query correctly using the expression you
posted below: GreenPct: -Sum([Green In Reg]) / Count(*)
Click to expand...

Copy and paste that exact expression into a vacant Field cell in the query
grid.

If that's not working, open your query in design view; select View... SQL from
the menu; and copy and paste the entire SQL string to a message here. Indicate
what's not working and how (error message? wrong data? ...?)
 
It worked this time. I obvioulsy had ssomething wrong earlier, but it is
working now. Thanks a million for the fix!
--
Respectfully,
Robert F.


John W. Vinson said:
Can you please
explain how to setup the Totals query correctly using the expression you
posted below: GreenPct: -Sum([Green In Reg]) / Count(*)
Click to expand...

Copy and paste that exact expression into a vacant Field cell in the query
grid.

If that's not working, open your query in design view; select View... SQL from
the menu; and copy and paste the entire SQL string to a message here. Indicate
what's not working and how (error message? wrong data? ...?)
Click to expand...
 
Back
Top