Formula dilema

[Tony in Michigan]

I have a number of data fields which are two decimal currency values stored
as numbers. The fields are all associated to specific files [FIELD_FILE]

in order to validate the data against a zero decimal reference value I need
to sum all the related [FIELD_FILE] values and compare to the reference.

The problem I have, is that the reference value was generated by summing
rounded values.
1.51+1.49+1.49+1.49=5.98 rounded to 6, the reference value is 2+1+1+1=5
How can I do this in access?

Right now I do the following
UPDATE Q1: Round([field],0) populate new FIELD_ROUND

MAKE TABLE Q2: group by [FIELD_FILE] sum [field_round] Gives me a subset
of values to evaluate

Q3: I validate the above by comparing the reference value to the summed
value from Q2 [reference]-[summed_field_round] = 0 valid

How can I clean this up? and what references are there for learing mor
involved queries and formulas?

 

[KARL DEWEY]

1.51+1.49+1.49+1.49=5.98 rounded to 6, the reference value is 2+1+1+1=5
How can I do this in access?
Your reference value rounded prior to summing so you must do the same.
Reference_Check:
Round([field1],0)+Round([field2],0)+Round([field3],0)+Round([field4],0)

 

[Tony in Michigan]

Hi Karl,
My biggest problem is the fact that it takes so many steps using my current
method, and I know that there is a more robust, and direct method of doing
it.
Maybe a better way of stating the problem ...

In Access SQL,
how would I
round [field1],0 then sum the result and update [field2] which I would then
compare to [field3] and flag as being either = or <> in [field4]

How can I do this in access?
Your reference value rounded prior to summing so you must do the same.
Reference_Check:
Round([field1],0)+Round([field2],0)+Round([field3],0)+Round([field4],0)

I have a number of data fields which are two decimal currency values stored
as numbers. The fields are all associated to specific files [FIELD_FILE]

in order to validate the data against a zero decimal reference value I need
to sum all the related [FIELD_FILE] values and compare to the reference.

The problem I have, is that the reference value was generated by summing
rounded values.
1.51+1.49+1.49+1.49=5.98 rounded to 6, the reference value is 2+1+1+1=5
How can I do this in access?

Right now I do the following
UPDATE Q1: Round([field],0) populate new FIELD_ROUND

MAKE TABLE Q2: group by [FIELD_FILE] sum [field_round] Gives me a subset
of values to evaluate

Q3: I validate the above by comparing the reference value to the summed
value from Q2 [reference]-[summed_field_round] = 0 valid

How can I clean this up? and what references are there for learing mor
involved queries and formulas?

 

[KARL DEWEY]

My biggest problem is the fact that it takes so many steps using my current
method,
All you need is one query so why do you say ' it takes so many steps'?

round [field1],0 then sum the result and update [field2] which I would then compare to [field3] and flag as being either = or <> in [field4]

There is no need to update [field2].

Post the SQL statements of your queries so someone can look and streamline
the process.