Update query

  • Thread starter Thread starter Noel
  • Start date Start date
N

Noel

I need to take two fields and subtract the two and then the total I want
updated into a field.
QuarterlyCost - QuarterlySubsidary=TotalCostwithSubsidary.
I have an update query that updates all of my costs. I have tried to enter
the equation a couple of ways and it's not working. I'm a little clueless
with the SQL so I need help.

Thanks
 
one point of view is that a calculated field never needs to be recorded into
a database because it is redundant. you have the elements that determine it
already in the database and thus can create this value in any form/report
whenever you need.

having said that; generally if you are to record a calculated value - it is
best to create/record this value in/via the form for which the elements are
entered; thus if they are changed this calculated value is changed also. so
an update query would not involved.

And having said that; if the table exists and the calculated value field is
blank or incorrect and you want to update the entire table...then you would
need an update query ; make one with the appropriate table and add only the
column/field you wish to update. In an UpdateQuery is a row ‘Update To:’ in
this space you put the math using the column names in brackets:
[ThisField]-[ThatField]
 
I have an update query built with all of the fields that I want updated. I
tried to add the [Quarterly_Cost]-[QuarterlySubsidy] to the "Update to" line
and I get the following error: "The specified field "[Quartlery_Cost]" could
refer to more than one table listed in the FROM clause of your SQL statement.
I know I have "Quarterly_Cost" listed in other tables, but what do I do to
correct this?


NetworkTrade said:
one point of view is that a calculated field never needs to be recorded into
a database because it is redundant. you have the elements that determine it
already in the database and thus can create this value in any form/report
whenever you need.

having said that; generally if you are to record a calculated value - it is
best to create/record this value in/via the form for which the elements are
entered; thus if they are changed this calculated value is changed also. so
an update query would not involved.

And having said that; if the table exists and the calculated value field is
blank or incorrect and you want to update the entire table...then you would
need an update query ; make one with the appropriate table and add only the
column/field you wish to update. In an UpdateQuery is a row ‘Update To:’ in
this space you put the math using the column names in brackets:
[ThisField]-[ThatField]

--
NTC


Noel said:
I need to take two fields and subtract the two and then the total I want
updated into a field.
QuarterlyCost - QuarterlySubsidary=TotalCostwithSubsidary.
I have an update query that updates all of my costs. I have tried to enter
the equation a couple of ways and it's not working. I'm a little clueless
with the SQL so I need help.

Thanks
Click to expand...
Click to expand...
 
I need to take two fields and subtract the two and then the total I want
updated into a field.
Click to expand...

No, you probably don't.

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 or just as you're now doing it -
in the control source of a Form or a Report textbox.
 
What do you mean by "When ever I need it?" We don't want it so that someone
has to manually run the calculation each time. I am building this for users
that only know the basics of entering information in the fields. I am not
SQL savvy either, so sorry if I ask alot of questions.

Thanks
 
What do you mean by "When ever I need it?" We don't want it so that someone
has to manually run the calculation each time. I am building this for users
that only know the basics of entering information in the fields. I am not
SQL savvy either, so sorry if I ask alot of questions.
Click to expand...

Users should not need to do the calculation, ever.

Users should not look at Table Datasheets, *EVER*.

Users should interact with the data via a Form (for onscreen display) or via a
Report (for printing). The Form or Report can be based on a Query which does
the calculation, or can contain a textbox which uses the calculated expression
as its Control Source. In neither case is it either necessary or appropriate
to store the calculated value.
 
I know they are not supposed to do the calculations and see the background
coding. They use forms to enter their data, but they have Command Buttons
that they use to do the queries and reports. I just need to know how to enter
this information in my update query so that it will calculate that and then
when they click on Update Costs command button (which is the update query) it
will calculate that field and update it to what it should be. I've tried it
and I can't get it to work.
 
I know they are not supposed to do the calculations and see the background
coding. They use forms to enter their data, but they have Command Buttons
that they use to do the queries and reports. I just need to know how to enter
this information in my update query so that it will calculate that and then
when they click on Update Costs command button (which is the update query) it
will calculate that field and update it to what it should be. I've tried it
and I can't get it to work.
Click to expand...

Just let me verify one thing first:

You understand why the calculated value should not be stored.
You realize that storing the calculated value risks data corruption with the
WRONG VALUE being stored.
You've rejected the approach of redoing the calculation on your Reports.
You want to go ahead and do it wrong anyway.

If you do, post back and I'll hold my nose and post code to do it.
 
Okay........... I am trying to enter the calculation in a query that pulls
the information into the field in the database from an excel spreadsheet.
The information from the excel spreadsheet is imported into the database,
then an update query is used to update the costs that are put into the
spreadsheet from our actuary. The calculation that I am trying to get is used
to calculate one cost from the spreadsheet and another cost that is in our
database already. So, I guess my question is , am I still doing this wrong?

Thanks
 
Okay........... I am trying to enter the calculation in a query that pulls
the information into the field in the database from an excel spreadsheet.
The information from the excel spreadsheet is imported into the database,
then an update query is used to update the costs that are put into the
spreadsheet from our actuary. The calculation that I am trying to get is used
to calculate one cost from the spreadsheet and another cost that is in our
database already. So, I guess my question is , am I still doing this wrong?
Click to expand...

Probably. The basic principle is that if a value can be calculated from values
which already exist in the table (whether it was imported or manually entered
or whatever), then it SHOULD be calculated, and not stored in the table at
all.

Your update query should be able to update both fields if you decide that is
what you want to do.
 
Back
Top