Calculation in form using 12 fields

[hayashig]

Hi,

I am new to Access and I am not sure if this problem fits into this topic as it might span into a few different categories. I am currently using Ms Access 2007. I have a table setup with 12 fields and form with 12 fields which is corresponding to the 12 fields on the table.

The 12 fields on the form are combo box with setting
Row source: 1;"Yes";0;"No"
Column Count: 2
Column widths:0";2"

If I am correct, this allows the user to select Yes or No and have a corresponding values of 1 or 0 inputted in the table.

Now the real question is that I need to make a field that sums up the values from the 12 fields in the form and records it onto the table.

So far I have set the following up in the code builder in the form

Private Sub Field1_AfterUpdate()
Me.Total = Me.Field1 + Me.Field2 + Me.Field3 + Me.Field4 + Me.Field5 + Me.Field6 + Me.Field7 + Me.Field8 + Me.Field9 + Me.Field10 + Me.Field11 + me.Field12
End Sub

Private Sub Field2_AfterUpdate()
Call Field1_AfterUpdate
End Sub

The Afterupdate on Field2 is repeated for Fields 2-12.

I have a hunch I did something wrong in the code and would appreciate any help in solving the problem.

Also, I am willing to undertake any other methods other than code builder if it yields better results as long as the Total field sums the values of the fields 1-12 and records it in the table.

 

[vbaInet]

Actually a Yes is "-1" and a No is "0". A bit strange right?

How does the field get updated if your Row Source is static values, meaning it's not bound? If it's not bound then when you change it, it wouldn't get saved. Or are you saving the changes in code?

What you can do is perform the calculations in the query instead (if you wanted). The (aliased) field in the query would look like this:

MyCalculation: CInt(Field1)*-1 + CInt(Field2)*-1 + CInt(Field3)*-1... so on and so forth

 

[vbaInet]

On second thoughts, if you're not using the built-in YES/NO datatype but rather you're saving your 1/0 values into a Number or the Yes/No value into a Text datatype then you would need the Val() function for ensuring that a number is returned.

Again, use a query to get the summation:

MySum: Val(Nz(Field1, 0)) + Val(Nz(Field2, 0)) + Val(Nz(Field3, 0))

 

[hayashig]

Thank you for your response VbaInet.

I have inputted the formula you have provided in the query of the field that is used to record the calculation and changed the "MySum" to the field that the summation would be recorded to and "Field1, Field2 etc" as the fields used in collecting the 1/0 value.

Formula given
MySum: Val(Nz(Field1, 0)) + Val(Nz(Field2, 0)) + Val(Nz(Field3, 0))

Formula used
Family History: Val(Nz(FH Mother, 0)) + Val(Nz(FH Father, 0)) + Val(Nz(FH Grandparents, 0))

The Formula was typed in the Field column of the query which the calculation is supposed to be recorded.

Also, I have read from other forums which for some reason leads me to believe that using query for calculation does not allow for recording of the information since the table will not be selected as a record source. I might be wrong since I'm an amateur in MS access. If query is not working, is there any other method of solving this problem?

 

[vbaInet]

Your field name should automatically be enclosed in square brackets after you move out:

Family History: Val(Nz([FH Mother], 0)) + Val(Nz([FH Father], 0)) + Val(Nz([FH Grandparents], 0))

It depends on what [FH Mother], for example, returns? Does it store Yes/No or 1/0?

Yes you're correct, doing calculations in a query doesn't store the calculation and if any values you calculate, you shouldn't store them. There are some exceptions to this rule though.

 

[hayashig]

Thank you vbaInet.

The [FH Mother] and all other fields record 1/0 value instead of yes/no.

I wanted to make the calculation recorded on the table since the value of the calculation is what is actually important which is dependent on the answers to the other 12 fields (FH Mother, FH Father, FH Grandparents etc.)

I only made the 12 non-calculated fields to make inputting the data easier for the other researchers since I will not be the only one using this.

 

[vbaInet]

Can you post a stripped-down version of your database so I can have a look?

 

[hayashig]

I attached the access file so I hope this will provide some information. I also removed all non-related fields since it took up way too much space for me to upload it. Again thank you for your assistance.

 

[vbaInet]

I couldn't see where you tried the formula I gave you?

See attached and have a look at your Database query and you will find that it works.

Also, your table called "Database" isn't a good name for anything in Access. "Database" (I believe) is a reserved keyword.

 

[CraigDolphin]

Here is a list of reserved words that you might find helpful:
http://support.microsoft.com/kb/286335

 

[hayashig]

Thank you for the replies,

I have made the query calculation by replicating the one you made, but is there anyway to see the solution of the calculation? Also, is it possible to see the calculation in a report when I need to do some analysis?

 

[vbaInet]

Use the query as the Record source of your form/report and set the Control Source property of a text box to that field.

 

[hayashig]

vbaInet thank you for all your help. The form and report are both working and I can see the calculation working the way I had hoped. Without your help this would not have been possible.

 

[vbaInet]

You're welcome hayashig. Glad that's working for you.