• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 358
  • Last Modified:

Updating a Running Total field after entering a new Item

Hi Experts,
I have a form where the user will enter a new record, which contains among other things a field for TotalLBS, and a field for LotNumber.   This form is based on a table called tblSHInv

I have a table that holds infomation about LOTS called tblLots (LotNumb, StartDate, EndDate, TotalLotLBS)

When the user saves the record, I would like to take the TotalLBS from the new record in tblSHInv and add it to the TotalLotLBS field of the current LotNumb in tblLots.  

My question is, is there a better way to update the TotalLotLBS value other than building a query and then running that query when they save the record?

Also wondering if I'm missing something in keeping a running total of an item in a field.... or should I build some sort of transaction table and total from that.   Looking for correct method.

Thanks for any help.
0
TechGuise9
Asked:
TechGuise9
  • 6
  • 4
1 Solution
 
Helen FeddemaCommented:
Running totals are a feature of reports, but not of forms, so you will need to do something with a query or SQL to get a running total on a form.  One way to do this is to put a little subform on the form, to display the calculated running total.  Requery it as needed, say from AfterUpdate of a relevant field, or AfterInsert of the form record.
0
 
TechGuise9Author Commented:
I guess I didn't explain it well enough.   I am needing to update the value of a field in another table based on the record just entered.

TotalLotLBS = (TotalLBS + TotalLotLBS)

Getting a total on a FORM is not an issue.
Thanks
0
 
Jeffrey CoachmanMIS LiasonCommented:
Typically "calculated" data is not stored.

...Can you explain the need for this...?
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
TechGuise9Author Commented:
Hi Boag,
When the user first opens the FORM where they enter a new inventory item, I've built a routine that checks the accumulated total of the current Lot#  (tblLots, field = TotalLotLBS)

If the TotalLotLBS are over a prescribed amount, it prompts the user to create a new Lot#.  

I'm sure I've not been following good practice in the past with things like inventories when it comes to running totals.... but this is the way I've been doing it.   Sugestions?

Thanks
0
 
Jeffrey CoachmanMIS LiasonCommented:
...Sorry, I'll explain.

If you store calculated values, you set yourself up for having to create systems to update these values continuously if any precedent values change.

Typically a query is created and the value is calculated there.
This query can, in turn, be used just like a static table, only no "update" system need be created.

So again, can you provide a bit more info on this system?

Jeff
0
 
TechGuise9Author Commented:
The system is to track new products in a food manufacturing facility.   Each new product coming off the line assigned to a Lot# (used in case of a re-call).  

So the lbs of each new inventory unit is added to the associated Lot#.  

So if I am understanding you correctly, I should not store the accumulated Lot lbs but instead look to a query that provides a sum of the lbs.
And if I'm still understanding you correctly, if I were using the same concept with a "quantity on hand" scenario for an inventory item, I would use a query to add all the credits to inventory, subtract all of the debits from inventory and that (using a good beginning qty) should give me a good QOH total.

I guess the reason I never considered doing that is that is seems like these queries are going to be working harder and harder as the years go by.   Meaning the # of records they will be summing will get longer and longer.

0
 
TechGuise9Author Commented:
Hi Jeff,
I've just took a look at the Northwind database queries.   I guess I was understanding you correctly.  

For some reason the concept is hard for my brain to trust, but I guess as long as we know all that came in and went out, it's foolproof.

Now I guess I need to know how to pass some criteria to a query using VBA (to get the current Lot#)
Should I open another question?
0
 
Jeffrey CoachmanMIS LiasonCommented:
Actually you provided just as good of an explanation as I could have...
;-)

Every situation is different, ...that why most Experts will say "In General you should..." (or something similar), and not speak in absolutes (you should never...")

In a nutshell "Stored" calculations will require systems to keep them up to date for all data entry scenarios, whereas a query will not.
(ex. You create a system to store the calculated value in a table when the record is "created" in the form.
Great, ...but what if a person "modifies" a record?
What if they don't use the form, but instead use the table...
Or what happens if you have to bypass the form for testing purposes...
Your stored calculation may no longer be valid in theses scenarios...

This being said, there are times when storing calculations might be better
*IF* the calculation turns out to take and excruciatingly long time to complete (the key word is IF, because only time and other factors will contribute to this)
Also if the "Calculation" is fixed.
An example would be a Death Database, where instead of calculating the Age at death, it is stored. (The date of death and the date of birth will always be the same)

On the other hand a persons "Current age" should be done in a query.
Make sense?

I was difficult for me to understand at first as well, but a Query can be used in almost every situation that a table can, with the benefit of doing calculations.

;-)

JeffCoachman

0
 
TechGuise9Author Commented:
Yes, makes a lot of sense.   Thanks for taking the time.

And.... You answered my question of "what if they need Period Ending Balances?"   (i.e things that will never change)

Thanks again!
0
 
Jeffrey CoachmanMIS LiasonCommented:
Just to be clear, ...as I stated, each situation is different...

Let's say that the Age at death is stored (because neither the DOB nor the DOD dates will ever change)
Now let's also say that the DOB was entered incorrectly...
Wen the DOB is updated you need an *Iron-Clad* system to Re-update and store the value, under all data entry scenarios...

Again, just FYI.

;-)

JeffCoachman
0
 
TechGuise9Author Commented:
Understand.  thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now