?
Solved

Auto calculate

Posted on 2012-09-03
9
Medium Priority
?
563 Views
Last Modified: 2012-09-06
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 mumps: yes
Have you had measles: No
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.

Thank you in advance
0
Comment
Question by:Reyesrj
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 38362429
You can use an expression like this:

=Abs([Have you had chicken pox]+[Have you had mumps]+[Have you had measles]+[Have you had pink eye])

/gustav
0
 
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
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

by:Jeffrey Coachman
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:

=Abs([Have you had chicken pox]+[Have you had mumps]+[Have you had measles]+[Have you had pink eye])

 ....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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 

Author Comment

by:Reyesrj
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

by:Jeffrey Coachman
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

by:Reyesrj
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

by:
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

by:Reyesrj
ID: 38371106
Thank you.<br />This works for me.
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 38371356
You are welcome!

/gustav
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question