Re: Simply adding two fields

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.


"John Spencer" wrote:

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
..

"Pat Backowski" <PatBackowski@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:4E59ECFE-F54E-4360-A863-3EA9EB411452@xxxxxxxxxxxxxxxx
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: Simply adding two fields
    ... It doesn't save disk spins and cpu cycles. ... John Spencer ... 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 ...
    (microsoft.public.access.queries)
  • Re: Corstabquery or normal query
    ... You can create the crosstab similar to the suggestion by John. ... church, also church elders, we have many elders, only 1st elder is one person. ... I would first build a query to get the data ...
    (microsoft.public.access.reports)
  • Re: Creatinig a database to update in alphabetical order
    ... John M. it is like you are reading my mind, ... know I am a movie fanatic. ... > should not be routine) displayed as a query datasheet. ... > Keyword ...
    (microsoft.public.access.gettingstarted)
  • Re: problem with query
    ... Thanks John, it is not a number field, it is text. ... Open tblSitLog and look at the field SIT - what type of field is ... You may need to write the query as follows ... , tblSITLog.[NATURE OF iNCIDENT] ...
    (microsoft.public.access.queries)
  • Re: set first record to zero in a calculated field
    ... John, after playing around with the FOrmat comand, I think I managed to get ... I resolved it in my previous query by using the Format command in the SQL, ... Center for Health Program Development and Management ...
    (microsoft.public.access.queries)