Query of checkboxes.

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?

A

Allen Browne

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.

J

John W. Vinson

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?

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(*)

R

Robert F.

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?

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(*)

J

John W. Vinson

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

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? ...?)

R

Robert F.

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:
explain how to setup the Totals query correctly using the expression you
posted below: GreenPct: -Sum([Green In Reg]) / Count(*)

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? ...?)