Auto calculate
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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.
In other words, can you first evaluate the posts by cactus_data and I, and tell us what your thoughts are.
ASKER
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”.
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”.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you.<br />This works for me.
You are welcome!
/gustav
/gustav
=Abs([Have you had chicken pox]+[Have you had mumps]+[Have you had measles]+[Have you had pink eye])
/gustav