Re: Simply adding two fields

Tech-Archive recommends: Speed Up your PC by fixing your registry

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.

"John W. Vinson" wrote:

On Thu, 22 Nov 2007 06:07:02 -0800, Pat Backowski
<PatBackowski@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

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?

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]

.

Relevant Pages

  • Re: Update and Append Query
    ... UPDATE Master RIGHT JOIN [Transaction] ... "Tom Lake" wrote: ... One query will do both. ...
    (microsoft.public.access.queries)
  • Re: Simply adding two fields
    ... In an update query you update the field to the calculation. ... field before you add it to the master field you will have a problem. ... I'm reading a transaction file of widgets delivered and I want to update ...
    (microsoft.public.access.queries)
  • Re: Simply adding two fields
    ... 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 ... Just redo the calculation whenever you need it, ...
    (microsoft.public.access.queries)
  • Re: Update and Append Query
    ... One query will do both. ... >> Tom Lake ... lets say you have a Master and a Transaction table with Trans ID being ... Master table to an Update query, then add the Transaction table. ...
    (microsoft.public.access.queries)
  • RE: Field result duplicates following join
    ... Forinformation I think you use the group by function on a query on the ... From another calculation I have a query result ... Date Movement Transaction Total Move RunSum ...
    (microsoft.public.access.queries)