Simply adding two fields

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I'm reading a transaction file of widgets delivered and I want to update the
quantity in the master record, so I do a query joining the master and
transaction data. That works fine. I've also created a new element in the
resulting table, lets call it answer. I want "answer" to be the sum of two
elements that are in the table, one from the master and one from the
transaction.

How do I do that?

Many Thanks,
Pat
 
Hi,

I'm reading a transaction file of widgets delivered and I want to update the
quantity in the master record, so I do a query joining the master and
transaction data. That works fine. I've also created a new element in the
resulting table, lets call it answer. I want "answer" to be the sum of two
elements that are in the table, one from the master and one from the
transaction.

How do I do that?
Click to expand...

Generally, you 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.

John W. Vinson [MVP]
 
John,

I was simply looking for some help in how to add 2 numbers together in a
query.
I was NOT looking for criticism on the age-old tried and true method of
Master-transaction updates.

Your response not only didn't answer my question, but you are wrong.
If I have to read 50,000 transactions every time some one wants to know how
what the balance of widgets is, there'd be a helluva lot of disk access going
on

Thanks for Nothinig
Pat.
 
In a query you just do
Field: Master.FieldName + Transaction.FieldName

In an update query you update the field to the calculation.

A problem here is that you cannot use aggregate functions in an update query
in Access, so if you are trying to sum the information in the transaction
field before you add it to the master field you will have a problem.

As John Vinson was trying to point out, you will have to be careful that you
keep the calculated data up to date. I understand your point about
accessing multiple records, but you might try the suggested solution and see
what kind of performance you are getting - you might be pleasantly
surprised.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Thank you, John. I was trying to add and update in one query, hence my
frustration.

I will keep in mind what you and John mentioned regarding not storing
derived data. I'm not sure how it would save disk spins and cpu cycles to
have to re-add balances, constantly, but I'm not too old to learn new tricks.

Many Thanks,
Pat.
 
It doesn't save disk spins and cpu cycles.

It does help to ensure your data remains correct (in sync with the data that
the sum is derived from)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Back
Top