Question about Access fields[RESOLVED]

[usamaalam]

Hi,

I have six fields in an access table, ID, num1, num2, num3, num4 and sum where ID is the primary key and all the other fields are intergers.

In last field sum, I want to store the sum of corresponding values of fields num1, num2, num3 and num4. This is an example, I have to do something like this, I just want to know how a calculated amount of different fields can be stored in a new field.

Thanks

[honeybee]

I don't think you could get an automatic way of doing it. However, you can write your INSERT/UPDATE statements such that they calculate the sum and store it in the last field, or write it in your AddNew or Edit code.

An example would be:

[code]
rs.AddNew
rs.Fields(0) = NewID
rs.Fields(1) = X1
rs.Fields(2) = X2
rs.Fields(3) = X3
rs.Fields(4) = X4
rs.Fields(5) = X1 + X2 + X3 + X4
rs.Update
[/Highlight]

.

[usamaalam]

I am doing the same thing uptill now, but someone told me that for doing this, you have to write a complex SQL query but I don't know the query and where to use it.

Looking for someone to solve this problem.

Thanks

[andreys]

It's not realy a complex quety:
Leave your fields as is ; ID, Num1, Num2, Num3, Num4 take out Sum. (By the way: do not call your field "Sum', it's name for aggregate function)

So next time when you are need to display data from your table just use sql statement:

SELECT *, ([Num1]+[Num2]+[Num3]+[Num4]) AS TotalSum FROM
TableName

txtTotal.Text=Recordset.Fields("TotalSum")