Rounding in Update Query

  • Thread starter Thread starter Betty
  • Start date Start date
B

Betty

I am using an update query to update a currency format field with a
currency format field. I have a "claims" table and a "claims detail" table.
I need to update the claim total from the sum of the details for each claim.
When I run the query, I lose the cents in the updates field. How may I get
an exact update?

Thank you.
 
You say you have a "a currency format field" but do not say the datatype of
the field.
Is it Number --
- Integer
- Long Integer
- Single
- Double
- Decimal ?
Try setting it to Double.
 
I am using an update query to update a currency format field with a
currency format field. I have a "claims" table and a "claims detail" table.
I need to update the claim total from the sum of the details for each claim.
When I run the query, I lose the cents in the updates field. How may I get
an exact update?

Thank you.
Click to expand...

You have 2 problems.
1) There is a difference between a Currency Datatype and a Currency
Format.
I would suspect that the Field you are updating is an Integer or Long
Integer datatype. By definition, an Integer is a whole number (no
decimals). Change the Field's datatype to Currency or Double.

2) Why are you attempting to store the Total value anyway?
This Total field ought not be a table field at all. Any time you need
the Total value, simply calculate it, in a query, or directly in the
control source of an unbound control on a form or report. Saving
calculated data goes against the ruled of Access design. Access is not
a bigger version of an Excel spreadsheet.
 
Firstly, if the claim total is always derivable from the sum of the claim
detail amounts then it should not be stored in a column in the claims table
but computed on the fly in a query whenever needed. Only if its legitimate
for the total to be altered independently of the sum of detail amounts should
it be stored. Otherwise its introducing redundancy and the possibility of
inconsistent data.

As regards the 'loss of cents' do you mean its rounding to an integer or is
the total showing a discrepancy in the cents compared with what you get if
you add up the detail amounts manually. If the former it suggests that the
data type of the column in Claims is an integer, but currency in claims
details. Both should be currency. If the latter, then this might be due to
the fact that the actual underlying detail amounts differ from what you see,
which may be the case if they result from some computation rather than being
manually entered. This is because the currency data type is to a precision
of 4 decimal places, but you see the values rounded to 2 decimal places.
This suppresses cumulative rounding errors, which is what's happening in your
case if this is the case. If you want the totals to be what you'd get if you
added the amounts up manually then you should sum the detail amounts rounded
to 2 decimal places, not the actual stored values.

Ken Sheridan
Stafford, England
 
Back
Top