Solved

# Auto calculate

Posted on 2012-09-03
Medium Priority
563 Views
Hi All,

I’m using MS Access 2007.

Here is a sample of what I need to do:

Have you had chicken pox: yes
Have you had pink eye: No

Total virus you have had: 2

In access is there a way to count the total “yes” in the 4 fields?  Or if the answer is “No” leave it blank and count the total fields that have a value?  So the question “Total virus you have had” will auto calculate.

0
Question by:Reyesrj
• 3
• 3
• 2
• +1

LVL 52

Expert Comment

ID: 38362429
You can use an expression like this:

/gustav
0

LVL 85

Assisted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 38363212
Can you give a little more information on how your tables are structured, and how you want to present this data? On a form, in a query, etc etc?
0

LVL 74

Assisted Solution

Jeffrey Coachman earned 500 total points
ID: 38364851
The solution posted by cactus_data will work if each Virus is it's own field and the number of Viruses will remain constant.
This design has a drawback in that as you add/remove/edit viruses, you must edit this formula:

....accordingly with each change

If this was a more "Normalized" design, this disadvantage goes away.
I see something like this (Attached sample) as a start.

JeffCoachman
Database37.mdb
0

Author Comment

ID: 38366423
Thanks for the great ideas.  I just thought of this please let me know what you think:
I’ll create a table with two columns where:
Col 1                    Col2
Chicken pox         1
Mumps                  1
Measles                  1
Pink Eye                  1
NULL                  0

Then I total column 2.  If this is possible how would I get the NULL to work?  I want this to reflect in the form so I’m guessing I will create a query and use the query as a field in the form.  I also need this in a report that will be queried from excel (excel is my thing so I can do this).

Thanks
0

LVL 74

Expert Comment

ID: 38366658
I can't comment on your design, ...as I am not quite sure what it will accomplish over what  cactus_data and I posted...

In other words, can you first evaluate the posts by cactus_data and I, and tell us what your thoughts are.
0

Author Comment

ID: 38366796
Cactus_data, I understand and I am able to do what you are suggesting.  How do I get a count of all the no blanks?  If that is what you are suggesting.

I have a form where users will enter data and be able to view data that has been entered.  The example I gave first, show some of the data that will be entered as well as demographic information etc.  There are about 25 different viruses that will be asked if the patient has or has had.  If a user goes to a patient record (currently) they need to count the total viruses the patient has or has had (count those marked “yes”).  What I am trying to do is have a field that will show the total count of the 25 questions that are indicated as “yes”.
0

LVL 52

Accepted Solution

Gustav Brock earned 1000 total points
ID: 38366886
Have a TextBox on your form.

Assign as ControlSource an expression like this:
=Sum(Abs([NameOfYourColumn2]))

/gustav
0

Author Closing Comment

ID: 38371106
Thank you.<br />This works for me.
0

LVL 52

Expert Comment

ID: 38371356
You are welcome!

/gustav
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
###### Suggested Courses
Course of the Month13 days, 16 hours left to enroll