calculation problem

[KenHigg]

'one summation pass through the transaction table' - nuts

Repeat: Do you know of any other authorities (other than ourselves ), that have documented their arguments on this issue, in a book or white paper?

 

[KenHigg]

Doc,

Quote: 'Sorry to take this attitude, but if that turned out to be a serious problem, you have tried to run your system on a machine too slow for your business needs.'

Could be you’ve developed a system that performs frivolous operations.
===============
Quote: 'And to be honest, locking records is more common than not in modern inventory DBs.'

No argument here. Sounds Good!
===============
Quote: Now, the parts table is NOT (IMHO) the place to put a running count of pumps, but it WOULD be a place to put a volatile LOCK marker.

I am not so much for putting the count in a parts table as I am against doing extraneous, 'summation passes'. I would buy putting it in say, an inventory table.
===============
Quote: 'Also, there is another way to do this...

Repeat: 'Of course, in either case you could lock a part number once a user has the part number pulled up in a transaction mode screen.'

Sounds good, except, well you know...
===============

While wildly crunching through thousands of records to figure out a value over and over and over and over, that could be stored, is my main objection to these 'summation passes', I do have a second primary concern.

The argument has been made that somehow, the current value that has been stored may not be valid because the system may have failed to store it correctly and that to get a true value you need to look at all previous transaction qtys.

If the system failed to store the current stock qty correctly, it may well have failed to store any one or more of the thousands of other values in the transaction table correctly. If you have thousands of transactions, you've just multiplied the chance by thousands that your calculated value is wrong...

ken...

 

[piet123]

simple yet complicated calculation problem...

Thanks everyone for replies.

The Doc Man, thanks for advice you posted in post#19. My friend, who manages a small Pest Control bussiness, and I are now back at the point where you suggested: "do it on paper first and then work towards the db".

As you all pointed out: it is VERY important the the calculation of the STOCK field be CORRECT at all times. BTW, how will existing records be affected if the PRODUCT PRICE value increases on a given date ?

Bottom-line is that I have to as Doc Man (professor) says or else I'll have Neil (accountant) on my case...

Like doc man says: it's back to the drawing-board for me.

I'll let you all know how my db turns out.

Thanks again everyone.
Regards,
Piet.

 

[KenHigg]

Oh well, looks like I got out posted on the stock qty issue...

Good luck Piet, Sorry to have wasted everyones time on your post - Hope your db works out well for you!

 

[Pat Hartman]

Here are just two of the many inventory questions seen here -

http://www.access-programmers.co.uk/forums/showthread.php?t=4117&highlight=Inventory
http://www.access-programmers.co.uk/forums/showthread.php?t=68407&highlight=Inventory+Cycle+Count

 

[KenHigg]

Ok, ok...

I'll cave on the inventory example, but...

I refuse to believe that just because you can get a value from previous records, that you shouldn't store a value. For example: I'll bet that the number of post and threads, etc that you see on the message board are not re-calculated every time a user loads a new page.

???

 

[Pat Hartman]

I am pretty sure the counts are calculated because there was a meltdown of the board a couple of years ago and my count dropped from over 2000 back to 0 even though the old posts were still available - but the important thing to note is that if the stored posts count is inaccurate, who cares (except me)! If your inventory is inaccurate, someone will care you can bet on it. You have a fiduciary responsibility to your employer or client to provide the most accurate data you can. It has been proven to me over and over again that storing calculated values leads to data anomolies. I have become an expert in finding bad data held by systems designed by others - this forum being no exception.

The cycle count compromise seems to handle the volumn problem and since the balance forward records are not updatable, they cannot be incorrectly changed. The archive table contains the detail required to support the balance forward should there be an audit.

 

[ChrisO]

I expect to get my backside in a sling for this but here goes anyhow…

There’s a lot of reading to do in this thread and I do apologize if I have not read it correctly.

My concern is based both on accuracy and speed.

Whatever method is employed and if accuracy is assured then why do a stock take at all?

Totally rhetorical question and here’s some more!

Someone steals stock and does not update the database.
There’s a fire and 35 of the 50 ‘Plastic Widgets” are destroyed.
A salesman removes a ‘Plastic Widgets” from stock to display to the customer.

The ‘more correct’ way to maintain ‘inventory balance’ would not handle this.

It seems to me that that is the reason for a periodical manual stock take.
(And if that manual intervention is required then the automatic version is limited on accuracy.)

So if we allow for the case of inaccuracy, by doing a manual stock take, then another system may be required.

Why not do both?

If the short-term speed requirement requires just that then display the last saved short-term balance. If accuracy requires re-calculation back to the ‘defined’ beginning, then do that too and update the short-term balance record. If there is a difference then save that calculated value as well, it then allows tracking of the difference between saved and calculated quantities.

These are just questions…I don’t as yet do inventory balances but I have done aging summary and there seems to be little difference. Eventually doing it the ‘correct way’ will lead to display delays.

A ‘given’ is to set it up correctly in the first place, then to de-normalize it for speed.
(In that way you will have an ongoing comparison.)

Another ‘given’ is to not take ‘givens’ no matter where they might be derived.

Just my thoughts…

Regards,
Chris.

 

[neileg]

Whatever method is employed and if accuracy is assured then why do a stock take at all?

Totally rhetorical question and here’s some more!

I don't think this is rhetorical at all!

There are two different potential errors here. The one we have been discussing derives from flaws in the design of the database. Even if data is correctly entered, the system may return the wrong figure. The second type of error occurs when incorrect data is included or excluded from the system. This may be through theft as you suggest, or through human error.

It is not sensible to suggest that we should excuse one error because the other is likely to occur.

In practice, when a physical stocktake turns up an error, you need to look at the transactions in the system to see if you can spot where things have gone wrong.

 

[Rich]

Someone steals stock and does not update the database.
There’s a fire and 35 of the 50 ‘Plastic Widgets” are destroyed.
A salesman removes a ‘Plastic Widgets” from stock to display to the customer.

We used the term "shrinkage" as the word you're looking for

 

[KenHigg]

'I am pretty sure the counts are calculated because there was a meltdown of the board a couple of years ago and my count dropped from over 2000 back to 0 even though the old posts were still available...'

I checked, they're stored. BTW - If there was a melt down, wouldn't you be lead to beleive they would be stored since a calculated value would always be available?

 

[Pat Hartman]

I mis-spoke. I assumed the counts were stored.

 

[The_Doc_Man]

Piet, I hope you are not too badly confused by what you have seen here. It merely proves that there is more than one way to skin a cat. (Egad, what a screech when you DO skin it.... )

As to changes in price over time, there is no other solution than to separate price from item number and make a table that holds prices (including historical prices) separately. It should have at least four fields, which would be the part number, the price, the date on which that price first became valid, and the date on which that price was superceded. As a default, you can pick a date 100 years away for "superceded price" when you first create the new price entry. That means the price is good from the start date until 100 years from now. Then, when you supercede the price, you have two records to deal with - the NEW price record gets created, and the PRIOR price record's supercede date (only) gets updated.

Now, the price on an invoice has to include the date of the invoice as a key to support linking to the price tables. But this price info gives you valuable historical data sometimes.

By the way, splitting that price out into a separate table also gives you some flexibility to put discount codes (fifth field, perhaps) in each price record.

 

[piet123]

just calculate the stock value

Piet, Are you more confused at this point or do you have a game plan?

FYI: Having a current stock qty field in an inventory table is fine. Just because you should theoretically be able to calculate some other flds and arrive a the same number does not mean you have to.

In my opinon, you should back up and do a proper data model or this db is going to get out of hand...

When / if you do a new data model, post it and we'll help you tweak it.

yes, and i still don't have an answer.

look at my db; say for example i have original stock value in products tbl(example 5000).

in record1 i ISSUE 1000. now the stock value(5000) in products table must be 4000, yes? so in record2 the (available?) stock value should now display 4000 (not 5000).

if in record2 i issue 4500, then the stock value in products table must be
-(minus)500. this is ok for MY needs.

but the problem: i get new stock (ex 10,000) and put 10000 in products table (stock field). How will my previous records be affected ?

am i thinking in the right direction ??

thanks for help.
Piet.

 

[The_Doc_Man]

Piet, the problem you describe is dealt with by normalizing the database. One of the problems underlying this higher-level problem is that you don't always see normalization issues for what they are. I'll take a different direction here.

It has been suggested that you should have a place to store the current inventory count for item X in the database record describing item X. But there is a hidden normalization issue with storing it ANYWHERE. You see, item X is actually item X at time T-sub-y, but ten minutes from now you might need to make it item X at time T-sub-z. In other words, your inventory record has an implied time field to qualify the value, and your running inventory count therefore does not reflect all of the keys that contribute to its value. This means that you have violated either second or third normal form (I ALWAYS get those two confused, but it is one of them.) The record in which it is stored does not contain enough information to accurately identify it. BUT - adding a time field to the master inventory record doesn't really resolve the problem because there is no OTHER master inventory record for the same item with a different time hack, is there? (If there is, all you are doing is moving the problem - not addressing it.)

Also, another normalization issue is that the value of an item is derived from transactions but the inventory master record is in a table separate from the transactions related to it. If you load the value of this total to the inventory master record, you now have to go to TWO places to do something with that item's counts. There is one and only one place where you always know the correct count, and that is by adding up the ISSUE and RESTOCK transactions for that item, with appropriate attention to the sign of the transaction.

If your concern is accuracy, storing the count ANYWHERE is wrong. If you are concerned about the difficulty of doing this using queries, storing the count in a separate place ADDS work - because if you have to rectify the count at any time, you have to run the totals query anyway.

One point that has been made many times, and it is valid, is that based on your inventory "churn" rate, you might wish to do a stock-count reset. Otherwise you have to process thousands of records after a while. (I hope your business is that brisk!) The steps here would be to pick a time when nobody is doing anything. You select an "Official" date and time for the reset, usually the beginning of some month before the current one. For instance, keep only 90-120 days of transactions. Your business rules will govern the problem here.

For each inventory item, you count the transactions up to (including equal) to that official date and time. You then store stock RESET transactions (pick another favorite name here) with the offical date and time and the count as of that official time. Put these in a temporary table that looks like a transaction table but is separate.

Now archive the transactions prior to (or equal to) the selected time and date. Put them in a history table that you can export to a file or a history database or whatever. Your choice. Now put the RESET transactions into your transaction database with the selected date and time of the official stock recount. Remove them from the temporary table when done.

What this does is put the count in the DB IN THE TRANSACTION TABLE with a time-tag that makes each count be BEFORE any other transactions you kept. This reduces the number of transactions to be summed when you do the summation query that defines your current stock count. And the count as of the count-reset date takes the place of a STOCK ADD transaction of equal count to the sum of all prior transactions. INCLUDING any previous COUNT RESET transactions from a prior archiving step.

It might make sense to store the counts somewhere, but if your problem is that you MUST ALWAYS BE ACCURATE, you can't do that. The stored count is accurate only until the next transaction, and nothing had better happen to stop you from storing that count. But if this is a shared database, having data in two tables means that you have to work HARDER to assure that you update the stored count BEFORE anyone else can perform a stock draw.

This is a manifestation of the old saying about "left hand not knowing what the right hand is doing." When data involves TWO tables and your disk drive has only one set of read/write heads, ONE of those tables will be updated before the other. Let's hope you get it right. And the problem in Access DBs on a shared file server is that usually, unless you write VBA code to directly muck recordsets, you don't always get the choice.

With due respect to the other posters who have made different suggestions, what I have told you is painfully accurate (I think). But as a veteran of the programming wars for 29 years, trust me in this one statement: The longer you wait to correct a design flaw, the more painful the correction becomes. If you get it right early, you hurt less later.

 

[The_Doc_Man]

As an afterthought, even if you don't have a formal file server, but have merely set up a share on an office workgroup-level network (not to be confused with Access workgroups), that doesn't change the problem of data access contention. The workstation hosting the share is, for that one shared disk or folder, acting as a file server.

Another point brought up by a poster related to SHRINKAGE transactions. The point is valid but is simply addressed by having an occasional inventory walk-through to reconcile DB count vs. shelf count, then issue transactions to rectify the count. Once you include shrinkage or adjustment transactions into your other transactions, your count is accurate again. One can never forget that the model is ONLY as good as the data you feed it. If you neglect to feed it, it gets rumbly in the tumbly and doesn't work so good.
(My grandson is into Winnie the Pooh! When little T. gets rumbly in HIS tumbly, he doesn't work so good, either.)

 

[neileg]

Can I throw in another complication, that of price. There are various models for pricing stock, all of which require different approaches.

The one that accountants like best is FIFO (first in first out). This assumes that the stock you issue is the oldest stock you have and that you current inventory is valued according to the most recent purchases. This is pretty challenging to program in a database.

You can have LIFO (last in first out). This assumes that issues are drawn from the most recent purchases. This is no easier to program than FIFO, and accountants don't like it!

'Average price' is a pretty fair compromise between the programmer and the accountant.

'Last invoice price' is the easiest to program and as a consequence is often used. As an accountant this one makes me itchy, but I live with it!

 

[piet123]

try and try again...

hi everyone,

can we please start this thread again...

- since may'04 (when we started this thread), i have tried many of the suggestions in the replies to this thread, and as i am no expert in access i repeatedly ended up at the beginning.

- if look at the history of this thread, you will notice that i need just a very small and simple db.

- since may'04 i did alot of reading and 'self-study' on access, and i'm sort of "getting the picture" now, but could find nothing to sort out my problem.

- most of you guys seem to know alot! about programming in access, and i have great respect for you... but if anyone could please suggest something SIMPLE! to sort this problem i'm having. (or is this wishfull thinking on my part?)

have a good day !
Regards,
Piet.

 

[neileg]

You want a small and simple database, but the problem is that stock management is not really a simple process.

The minimum you can get away with is two tables, one to hold the product details and one to hold the product transactions. The transactions you will need to hold would include:
- opening balance when the system goes live
- additions to stock, eg purchases
- deductions from stock, eg stock issues
- adjustments, eg errors found by physical stock taking

What is you need help about, now you understand the application better?

 

[KenHigg]

Piet, Are you still having problems with the current stock qty thing?

kh