Saving Calculations

[RitaMoloney]

Hi,

On my form frmOrders, I have a calculated control called UnitsonHand.This calculates the total units when a product is being ordered or re-ordered. This is the calculation:

=([QtyPerUnit]*[Quantity]+[UnitsInStock]).

I need this value to be stored in UnitsInStock in a table called tblStore.

When the user opens frmOrders and orders or re-orders a product the total unit will be calculated to the control called UnitsonHand. Now, if I had some sort of code to attach to the On_change event of this control, so that when the value changes it will automatically save it to UnitsInStock or if the value does not change it dose nothing.

I hope this makes since, I am sure this is easy to someone that know vb but I am just starting to learn it and I am not sure how to put this code together. If anyone could let me know I would really appriciate it.

I know that your are not suppose to save calculations but I really want to this time.

Thanks in advance,

 

[maxmangion]

why do you want to store the value in the table ? It is not recommended that you store calculated fields in tables.

 

[smercer]

By what Max means is:

if you want users to use forms all the time, (which is sensible to do) you just have a calculation and the form will do the rest. you can print it and whatever else. the main advantage is you do not have unneccissary space eaten by the database for each record. you may not think that a few records may not make a difference, but count up 10,000 records and you will wonder whether it was worth puting in.

also calculations can be performed in queries and reports with out storage space taken up.

so do you really need to store calculations?

 

[RitaMoloney]

Hi,

Thank you for you advise on storing calculations. I understand all of this and normally I am against storing calculations.

The reason I need to store this calculation is because on other forms in my db I need to use the value UnitsInStock to perform other calculations (I won't go into the exact detail of these calculations). As well as that, when I open frmStore, I have it automatically running some vb to check if the UnitsInstock value are less than or equal to the Re-order level value.

I really want to store this, please can you help me!

Thanks again.

 

[Mile-O]

The reason I need to store this calculation is because on other forms in my db I need to use the value UnitsInStock to perform other calculations

Then use the components of the UnitsInStock calculation to perform this other calculation.

Always go to the source:

NOT 6 * 3 if 6 is got from 2*3
So: (2*3) * 3

 

[smercer]

I really want to store this, please can you help me!

We are trying to help you avoid the pitfalls. what is going to happen when you have 10,000 records, you may change your mind and the calculation needs to be adjusted again for ALL 10,000 records.

What Mile has suggested makes a lot of sense. Nesting a calculation so you are doing it once and not more then that takes away the confusion. and that also helps us when you ask us to help you so we do not get confused

 

[Rich]

http://allenbrowne.com/AppInventory.html

 

[RitaMoloney]

Hi,

Thanks for all the advise, it took a little while but I got it working. You were right of course, it's much easier and less confusing to just do the calculation on the form.

Thanks again for all your help,
Rita

 

[KenHigg]

Rita, Would you mind sharing what tables you have in your db and what information they contain?

 

[Pauldohert]

I am really confused by this - Rita what solution did you go for?

What Allen Brownes example does is work out from a fixed "take stock" calculated field what the stock is now?

Why not then save the result as the new stock take with a date ( of course stock takes can be flagged as actual or database derived) - thus when the calculation is next performed - the process is that much smaller. - this avoids the problems of having to use method 2 which Allen goes through( and has the benefit of keeping the calculation as small as possible. (is this what is proposed to be done)

If not why not? I suppose an order could be changed after this date and the calculated value is then wrong (in allens example he would lock everything before this date?) but then in the invoices table would you not have to create a balancing record for what has been changed anyway to keep the accountant happy?

Or alternatively get the date of the record that was being edited and revert to the last stock take before that - and discard later ones. Do the calculation from there and save it again. But at least the last stock take date is still creeping forward (from the locked date as I see Allens working) to keep the calculation small..

so everytime I want to "see" the calculation I save it, in the hope its smaller in future - would this work?

 

[smercer]

Why not then save the result as the new stock take with a date?

Please review our previous posts before posting

 

[ChrisO]

I don’t know if this will help or simply ‘muddy the waters’, nor do I like quoting people but, from the original post…

When the user opens frmOrders and orders or re-orders a product the total unit will be calculated to the control called UnitsonHand. Now, if I had some sort of code to attach to the On_change event of this control, so that when the value changes it will automatically save it to UnitsInStock or if the value does not change it dose nothing.

Let’s try to throw some numbers at the problem and also assume that the correct starting UnitOnHand is 35 ‘Plastic Widgets’…

Customer rings and requests 10 ‘Plastic Widgets’…
Whatever event used on frmOrders and whatever calculation we use we should then have 25 ‘Plastic Widgets’ (35-10) UnitsInStock …
Customer rings back in 5 minutes and requests a quantity change to 8 ‘Plastic Widgets’ for that order…
FrmOrders re-calculates UnitsInStock as (25-8) ‘Plastic Widgets’ (17)

How does the FrmOrders event ‘know’ that in the second instance it should have added 2 and not subtracted 8?

The same sort of thing could apply when receiving stock. We receive 50 ‘Plastic Widgets’, as per the paperwork, and update UnitsInStock by (50). 17 + 50 = 67 ‘Plastic Widgets’ UnitsInStock. Storeman (storeperson?) upon opening the box discovers only 40 were supplied. Back to the data entry person…no problem I’ll let them know and simply change the incoming quantity from 50 to 40. ‘Plastic Widgets’ is recalculated as 67 + 40 (107) UnitsInStock.

Customer receives their (how many was that?) ‘Plastic Widgets’ but 4 are faulty so sends them back to supplier under warranty. Receiving adds 4 to stock 107 + 4 (111). No, no, no they can’t go back into stock (says the boss) because they are faulty. No problem, I’ll just take it out of the stock count. Receiving changes the last entry from 4 to 0. 111 + 0 = 111.

At the end of the day (and it has been a bad one) how many ‘Plastic Widgets’ are in stock?

That is just what I have read on the matter, but apparently things can get a bit complex.

When the dust settles, most of the above problems can be resolved by not saving the value of UnitsInStock.

Most but not all.
In some systems, speed of calculation is important.
I have not had the ‘pleasure’ of having to do stock inventory.
I have had the ‘pleasure’ of doing aging summary displays and reports.

To me they are both similar in that…
There are times that, when doing it the ‘correct’ way, produces a dog slow response.
Uses have to live with our abilities in all meanings of the word.
If those abilities produce ‘dog slow performance’ then our abilities need to be questioned.

If we can find a consistently accurate way, and the speed is acceptable to the user, then the question arises, just what is correct?

Regards,
Chris.

 

[Rich]

Please review our previous posts before posting

I don't see any mention of a stock take in any of your previous postings.

 

[RitaMoloney]

Hope this helps;

I have three tables called tblStore, tblOrders, tblPartsUsed.

tblStore
StoreID
PartName
ReOrderLevel
Discontinued

tblPartsUsed
PartUsedID
JobDetailID
StoreID
OrderID
QtyOrdered
PartsUsed


tblOrders
OrderID
OrderDate
QtyPerUnit
QtyDescription
Quantity
UnitPrice
Vat
Discount

I also have a Jobs table which contains job details and is linked to tblPartsUsed.

I have a Jobs form where you enter details about a Job. This form has a subform to the tblPartsUsed table where you enter the parts used (PartsUsed). When you enter an order (frmOrders) there is a subform to the tblPartsUsed table where you enter the parts ordered (QtyOrdered). The parts form has a subform to the tblPartsUsed table that show a history for each part of when it was ordered and what jobs it was used in. This form also shows the UnitsInstock, (a calculated valued, not saved.) by adding (use sum) the QtyOrdered and subtracting the used quantities (PartsUsed).

This is how it works for me. It clears up allot of the confusion and it is much easier than trying to save calculations.

Rita

 

[Pauldohert]

I have read the posts and was wondering why my proposed method wouldn't work - it is allen brownes method but with the calculation saved. As explained I think my method would handle Chris O for each change just do the recalculation from before the record giving problems ( no great work there - just call the function everytime a record is added, edited, deleted etc , all you need is the date of the earliest record to be altered, and use a stock take date before that to calculate the correct stock now.)

If the worst comes to the worst and record 1 is changed I have to do the whole calc again ( but this is only as bad as it being a pure calcuated field anyhow), but if the last record is changed I only have to calculate from just before that date.

I don't even have to worry if it was a delete , a downsize of an order, less parts coming in than perviously recorded, but my stock is always up to date, for the only reason of keeping the calc small.

There may well be a major flaw in this method, but what is it?

 

[KenHigg]

Rita, how far off would I be in assuming the following:

1. Your business has multiple stores
2. You sell parts to other businesses
3. Work is performed by your customers which are tracked by 'Jobs'
4. You need to track your inventory
6. You need to keep up with who a part is sold to and be able to tell them what job it was for.

Is this close?

 

[RitaMoloney]

Hi KenHigg,

The database is for tracking maintenance work done on boats.

We buy parts to be used on the boats.
Different parts are used for different jobs on different boats.
Need to track parts, and the amount of a part used, on what boat, and on what job.

Rita

 

[Wes Burnett]

saving calculated value to a field

I have a similar situation... we do monthly billing, we want to save the total now due for each customer into a field in the customer table we call "Pbal" for "previous balance." This is important, because the following month we will use that "Pbal" field to compare was what owed the previous month, how much credit was applied, get a balance forward then add the current month, calculate the total, and inform the customer of the new balance.

We also want the previous balance field in the customer table so we can do a quick lookup on that customer on what is currently owed.

Haven't seen any examples of how to save the calculated data and automatically place that data into the customer table field.

here's the customer table fields:

PDCustomerID
CustomerName
Contact
Address
City
State
Zip
Pbal

Thanks

Wesley W. Burnett
The Post Dispatch
Post City Television
Post City Radio

Post City, Texas