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!

 

[Uncle Gizmo]

Are these check boxes in a table or on a form?

If they are on a form, what view is the form in? and are they bound or unbound?

 

[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.

 

[Uncle Gizmo]

The best way and possibly the only way you're going to be able to count these records is with a query.

So are you familiar with queries?

How were you planning to see the results?

 

[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.

 

[Uncle Gizmo]

Ah I see, You have two tables, one with the check boxes in and another one where you want to store the results yes?

 

[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?

 

[Uncle Gizmo]

>>> where would I put the statements?<<<

What statements?

 

[HeavyK]

The Sum IIF statements ...

 

[Uncle Gizmo]

I Don't know, ask Brian.....

 

[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".