Formula dilema

  • Thread starter Thread starter Tony in Michigan
  • Start date Start date
T

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?
 
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)
 
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]


KARL DEWEY said:
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 said:
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?
Click to expand...
Click to expand...
 
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]
Click to expand...
Click to expand...
There is no need to update [field2].

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