taking the sum or difference of two columns and putting it in anot

  • Thread starter Thread starter mark
  • Start date Start date
M

mark

I have a database that I enter a number in two columns and need the
difference to be calculated and inserted in another. I want this to happin in
the form I use to enter and not in a query. I tried using a query but I get a
circlular reference error. I can get the right result in a query but I can't
get that result back to the table that I reference in my form. I believe I
need to use a "on update". please help.
 
mark said:
I have a database that I enter a number in two columns and need the
difference to be calculated and inserted in another. I want this to happin in
the form I use to enter and not in a query. I tried using a query but I get a
circlular reference error. I can get the right result in a query but I can't
get that result back to the table that I reference in my form. I believe I
need to use a "on update". please help.
Click to expand...


Slow down. That kind of calculated value should never be
saved in a table. Instead it should be calculated when ever
you need to display it or use it in another calculation.

Either do the calculation in the query or directly in a
form/report text box.
 
I have done it in a query. I need to eventually get a table that can be
accessed by an external program and it needs the sum of two entered values.
I'm trying to automate the process. The only way I can access with AutoCAD is
to have the result in a table. I have tried to enter info in one table and
query it and put it in another unrelated table. I get a circular error. We
used to to do this in school. The formula builder would allow us to basically
create the formula in the third column: ONUPDATE(column1 - column2) When
entering info in a form or table I would enter 3 (in column 1) and 1.5 (in
column 2) and result of 1.5 would instantly show up in the result column and
value would be current in the table. Just like a spreadsheet. This was
Several versions ago and I can't figure out how to do it now
 
I have a database that I enter a number in two columns and need the
difference to be calculated and inserted in another. I want this to happin in
the form I use to enter and not in a query. I tried using a query but I get a
circlular reference error. I can get the right result in a query but I can't
get that result back to the table that I reference in my form. I believe I
need to use a "on update". please help.
Click to expand...

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query as you're now doing it or
in the control source of a Form or a Report textbox.
 
Are you saying that AutoCAD can not retrieve the data from a
query? Many applications' import feature can import from a
query the same as from a table. Access's Import and Export
features don't even have to be told whether it's working
with a table or a query.

If worst comes to worst, I suggest that you run an UPDATE
(or, yuck, a make table) query just prior to making it
available for external use.

What you did before was use a form. Then the AfterUpdate
event of both data entry text boxes can update the third
text box:
Me.t3 = Me.t1 - Me.t2
BUT, there are many other ways to edit a value in the fields
bound to the t1 and t2 text boxes and you don't have a way
to run that code in every scenario, which will make the
third field's data incorrect. You may not intend to edit
the values in any other way, but you can not prevent it from
happening.
 
Back
Top