Creating a SUM field

[pbarnes]

I feel like this is a stupid question but here goes:

I just created a large database of which a few fields are going to represent "total" fields as they are the sum of a few other fields.

I attempted to build an expression in the "update to" box in an update query but it does not seem to be working. I tried creating a simple addition expression and it tells me that I am about to update XXX number of fields but when I look at the data afterwards no changes have taken place. I am not familiar with how to use the Sum function so it was not working for me.

I'm not sure if I'm going about this the wrong way but if someone could push me in the right direction I'd appreciate it.

Thanks.

 

[rainman89]

its best not to store calculations Unless there are certain circumstances... instead run the sum query when needed....

 

[pbarnes]

What I'm looking for here is something similar to what you can do in Excel by simply using the SUM function (selecting each cell to be summed). I want to be able to do the same thing with this SUM field in Access. I am trying to do a sum across fields for each record.

The name of the SUM field that I created is "Gross Paids" and the fields I would like to add together to populate "Gross Paids" are "Gross Loss", "Gross Exp Adj Int" and "Gross Exp Other", all of which are in the table "TEST".

In an update query I include the field "Gross Paids" and in the "update to" box I entered the following string: "Sum([TEST]![Gross Loss]+[TEST]![Gross Exp Adj Int]+[TEST]![Gross Exp Other])"

When I ran the query it gave me an error message that said "You tried to execute a query that does not include the specified expression 'Gross Paids' as part of an aggregate function."

What am doing wrong here?

 

[rainman89]

see if this link might help you
http://www.access-programmers.co.uk/forums/showthread.php?t=133241&highlight=sum+function

 

[pbarnes]

Thats not really what I'm trying to do...that thread talks about putting SUM functions in reports.

I figured it out though...because not every field that I was trying to add together had a value in it (Null) it was unable to add them together. I created an update query to say that if field was Null to update with "0". Once I did this I was able to do simple arithmetic in the update query.

Thanks for your help.

 

[rainman89]

further info on that is if u use NZ([fieldname]) it will return a value for you even if it is blank