Calculation of checkboxes ticked in a form

[Suzy]

I hava table named tblPeople with the following fields:
Id Primary Key
Name
Tea......checkbox
Coffee.....checkbox
Water.....checkbox
Juice.....checkbox
I would like to have a query which will tell me how many times each item is ticked. One person can tick more than one check-box. I can calculate each item in separate quries, by grouping them and counting Id but I am unable to use one query to do all that. Is it possible to do this? Can someone help me please?
Many Thanks

 

[neileg]

Just use SUM. True is held as -1 and false is held as 0. So if the SUM is -23 you know that 23 people have said yes to that beverage.

 

[Mile-O]

Personally, I'd normalize as you have a many-to-many relationship that you are trying to pass off as a one-to-many.

 

[Suzy]

Didn't quite get the result.

Just use SUM. True is held as -1 and false is held as 0.

It worked only for the first field, for the rest it was Yes or No. There were no numbers for the other three fields.
Any suggestions?

 

[Suzy]

What does that mean Mile-o-Phile?

I don't understand much about database. Can you explain please?

 

[Suzy]

It is ok but comes with a -ve sign

Just use SUM.

How can I get rid of the -ve sign in front of the numbers. In other words make them positive numbers.
thnaks

 

[Mile-O]

Well, here's the table structure for what I mean - your fields named after drinks is a repeating group and is advised against. For more information you should do a search on Normalization both here and on Google.

tblPeople
PeopleID - (autonumber; primary key)
Forname - (text)
Surname - (text)

tblBeverage
BeverageID - (autonumber; primary key)
Beverage

tblPeopleToBeverages
PeopleID - (Number; foreign key)
BeverageID - (Number; foreign key)

Both fields in the last table form the primary key.

 

[Jon K]

How can I get rid of the -ve sign in front of the numbers. In other words make them positive numbers.

You can either use the Abs() function or just multiply the Sum() expression by -1, i.e.

Abs(Sum([Tea]))

or

-Sum([Tea])

 

[Notsogood]

that was just perfect.

Your idea was great. Many Thanks.