 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / November 2007Simply adding two fields
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, > [quoted text clipped - 6 lines] > >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] 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. > >Hi, > > [quoted text clipped - 22 lines] > > John W. Vinson [MVP] 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.  SignatureJohn Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County . > John, > [quoted text clipped - 42 lines] >> >> John W. Vinson [MVP] 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. > In a query you just do > Field: Master.FieldName + Transaction.FieldName [quoted text clipped - 57 lines] > >> > >> John W. Vinson [MVP] 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) SignatureJohn 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. [quoted text clipped - 75 lines] >> >> >> >> John W. Vinson [MVP] |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.