Counting check boxes

[HeavyK]

I have check boxes YES, NO, and NA. What I want to do is count each yes, no, or na check boxes and total each. I have in my table "Total Yes", "Total No", and "Total NA". I am new to Access and I am at a stand still. Can somone please help?

Thanks!

 

[HeavyK]

Hi Giz:

Thanks for the reply. .. The checkboxes are in a table but are also part of my form. Being new to Access I'm not sure how to answer your next question (bond or unbond).

Many thnaks..

 

[HeavyK]

By the way Giz .. The form is unbound.

 

[HeavyK]

Yes, I am familiar with queries. The result that I would like to see is that everytime that a Yes, No, or NA is checked, it should total each checkbox field and place that number into fields called "Total Yes", "Total No", "Total NA" respectively.

 

[HeavyK]

Actually Giz .. There are in one table. Should I have 2 tables. Is this a better way to go?

 

[Brianwarnock]

This is basically a Sum If, although for "Yes" you could just Sum the checkboxes and multiply by -1.
Total No: Sum(iif(checkbox=0,1,0)
TotalNA : Sumu(IIf(Checkbox is null,1,0)

Brian

EDIT having seen the last 2 posts I think I've lost the plot, I thought it was for a report.

 

[HeavyK]

Giz:

Excuse me for being ignorant, but where would I put the statements?

 

[HeavyK]

The Sum IIF statements ...

 

[HeavyK]

Okay .. Thanks for all your help Giz, and nice conversing with you.

 

[Brianwarnock]

The statements go in the field row of a query, if you prefer just put the IIF part and then do a totals query, selecting Sum, however as I indicated in the EDIT of my post this is not what you are doing, you, I think want some dynamic totalling somewhere.

Brian

 

[Brianwarnock]

I cannot leave it like this, what you are in effect attempting to do is store calculated fields in a table, which contravenes the rules of decent DB design or normalisation as it is known. Any time you require these totals you can run the query and avoid all the problems of data integrity that storing calculated fields risk

Brian

 

[raskew]

Anyway, something like this will give a count of Yes and No responses:

SELECT
    Count(tblAccounts.Display) AS CountOfDisplay
  , Abs(Sum([Display])) AS TotYes
  , [CountOfDisplay] - [TotYes] as TotNo
FROM
   tblAccounts;

Bob

 

[HeavyK]

I appreciate all responses to my question but what I my database is, is very simple. I have a set of say 40 questions in which the user should answer, Yes, No, or N/A. After that had answered the questions, I need to total all the Yes, No, and N/A that were answered. Does anyone know of a better way to do this ... Any help would be greatly appreciated.

 

[Brianwarnock]

I have a feeling that we are at cross purposes here, is there any chance of you attaching a sample of your DB or at least descibing the table layout.

Brian

 

[HeavyK]

I am not able to attach the MBD file for some reason .. When I clicked on the Attach file paper clip, I was able to upload the file. Maybe you can reteive it? It's called "test.mdb".

 

[Brianwarnock]

There is no paper clip, what happened when you tried to attach. Is it too big?
Compact and repair in case it is. can you zip it?

Brian

 

[HeavyK]

When open to reply to your post, the only thing I see at the top is a attachments paperclip. Is there another way to upload my file?

 

[Brianwarnock]

Scroll down from the response box and use "manage attachments"

Brian

 

[HeavyK]

Thats exactly what I been trying to do, but I still can not attach the MDB. It's only 3MB. I am going to send it from another location in about 3 hours. I am at work and they maybe blocking the MDB extensions. I tried changing the file extension to TXT but I still could not attach it. Any other ideas?

 

[Brianwarnock]

can you zip it?