please help, (using a2k)

I have a small db to manage a Pest Control Store room.

In the attached db you will find the applicable tables, queries, and a form.

In the PRODUCTS table I have field (value) STOCK(current).

See Form JOBCARD ! : When I ISSUE and amount of Pest Control Poison to one of the operators (a PCO), then that ISSUED amount must automatically be deducted from the Products Table (STOCK field).

ie. as soon as an amount of poison is ISSUED, the Products table STOCK field must (immediately) be updated (become less).

Hope you know what i mean.

Any one that can help ? Please !

Thank You.

Piet.

not sure if i understand you correctly but place the following in the Available stock field on your form

=Forms!JOBCARD!STOCK-Forms!JOBCARD!ISSUED

Alternatively you can create the calculated field in the query, and then make the available stock on your form based on the newly created calculated field in the query!

not sure if i understand you correctly but place the following in the Available stock field on your form

=Forms!JOBCARD!STOCK-Forms!JOBCARD!ISSUED

Sorry for not explaining better...

Yes, thank you; BUT! it now only 'diplays' (and not calculate?) this:

- STOCK field display(gets) value from PRODUCTS table! example 5000; (for record1).

- In the ISSUE field the user enter value example 1000.

- AVAILABLE STOCK field is result of =Forms!...etc (as in quote). 4000.

...but for Record2 (or a New record) the STOCK value still displays the "original stock value" (5000)

WHAT I NEED is : for each new record, when i select a Product, the STOCK value(in the PRODUCTS Table!) must already have "decreased" (because of previous records).

I hope I explained better now because i'm still very new to access2k.

Thanks for any help.

You should not store your stock value at all, you should always calculate it. This avoids all sorts of problems, like the ones you have now.

In general, the "correct" way to do inventory is through a summation query that might include both stock-add and stock-remove transactions.

You build a TRANSACTION table that lists stock number, action, and amount added or removed from stock, plus a date and time. Plus whatever else you need, like the employee number of the person removing stock if that is relevant. But for inventory on-hand, you won't need that field. For other things you might, so don't ignore it. Some folks build TWO tables, one for adds and one for removals. This is written with that idea in mind.

Now to get your running inventory, make a query that is the UNION of two or more parts:

SELECT STKNUM, QTYADD as QTYAVL, DATEOFADD as XDATE from STOCKADDS
UNION
SELECT STKNUM, -QTYREM as QTYAVL, DATEOFREM as XDATE from STOCKREMOVES;

If you have a return of stock, you need another SELECT line showing the returns, which you could keep in a separate table if you needed it that way. (I wouldn't, I'd lump adds and returns, but that's just me...)

Now you make a query that does

SELECT STKNUM, SUM(QTYAVL) from MYUNIONQUERY, GROUP BY STKNUM;

(or something very much like this).

Now you have a list of current on-hand inventory. You can query this query for a specific stock number to get individual on-hand amount.

To do the same thing but for a given date, you can run the union query where you select for all dates BEFORE a given date, which you might be able to make into a parameter query (q.v.).

As a formality, if you wish, you can build a place-holder record, doesn't matter in which table you put it (but I would put it in the adds table), that contains 0 quantity available but allows you to define a new stock item on your books for the first time. Also allows you to have an item on the books before you receive your first shipment thereof. Or even if you have zero on hand at the moment.

Now, the business about updating to reflect adds or removals...

If you create a new record in the add table or the removal table, the union query just includes the new transaction automagically. No gyrations, no worry about whether it is there. It will be.

Some people go through an added step, not to be sneezed at, in which you archive your stock tables. You would use the "sum of all records before date X" query to build stock-on-hand records (again, probably to eventually be stored in the add table) for each stock item. But in this case, I would put them in a temporary table first and build all such on-hand records. Then and ONLY then, I could go on to the next stage, removal of the old records.

Put a stock-on-hand-at-archive-date-XDATE record in your add table, then copy and later delete all records older than the archive date from your primary tables. This is solely done to reduce the number of records you have to process at time XDATE + some number of months later.

Note that the XDATE for your archiving date is the date just before the first record you KEEP in the current table. I.e., it is the date on which that total is accurate. The ONLY date on which that totals record is accurate.

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.

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, I AM VERY MUCH MORE CONFUSED THAN EVER !!Maybe I didn't mention that I have VERY LITTLE! know-how of a2k (and don't have money to attend a course in Access).

Thanks for all the replies and Thanks to KenHigg for the offer to help.
I guess I'll have to start all over again ( it only took me like about a WEEK) to get as far as my db (attached in my first post).

Have a nice day everyone !

Regards,
Piet.

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.
If it's your database, you don't have to do anything anyone else tells you. However, it's very bad design to store a balance value like this. You can't tell what transactions are included in the balance, you can only see the current balance and never backtrack. If your routine for updating the balance runs twice, or fails to run at all, you cannot see that your balance is incorrect. This is more than enough justification for avoiding the stored balance.

If it's your database, you don't have to do anything anyone else tells you. However, it's very bad design to store a balance value like this. You can't tell what transactions are included in the balance, you can only see the current balance and never backtrack. If your routine for updating the balance runs twice, or fails to run at all, you cannot see that your balance is incorrect. This is more than enough justification for avoiding the stored balance.

Nuts - Store a snapshot of the current stock balance in the transaction table. Insane to calculate hundreds of thousands of records just to see a stock balance. The rule of thumb about not storing a calculated field is most relevant within a record.

Nuts - Store a snapshot of the current stock balance in the transaction table. Insane to calculate hundreds of thousands of records just to see a stock balance. The rule of thumb about not storing a calculated field is most relevant within a record.

Ken, there are indeed cases where you would do exactly this. And nieleg is also quite right, it is piet's db, not ours. That said, I don't want to tell piet123 something that will be confusing in the long run.

When you store a calculated value, there is always the question of whether you included some particular transactions in the mix or not at the time you took the snapshot. There is also this little gem of a problem: Now you have to date-sort the transaction records to know WHICH RECORD'S SNAPSHOT holds the most current value. But odds are that the date ain't the prime key, so there you are, sorting on secondary keys! AND - you have to sort on dates but also group by part numbers. This is very complex. It is equally as complex as doing the sum we described. EXACTLY equally complex. So if it is the same work, why NOT do the "right" work in the first place.

To put it in perspective, there is a reason for doing something the "right" way even if it is a little tedious. In this case, you want to avoid the confusion that occurs by having something stored in the records that is valid and correct only at a particular moment in history. I.e. that time after the transaction was processed and before the next similar transaction was processed. Because that is the ONLY time that the transaction's snapshot makes sense.

There is the other part of this - stock ADD transactions usually contain different data than stock REMOVE transactions. I.e. suppliers, customers, sales reps, purchasing reps, invoice numbers, purchase-order numbers. They link to different tables. Surely you don't keep suppliers and customers in the same table! So when I gave my advice, it was to avoid giving piet a solution that might become a straightjacket later.

My view is that the database should become a reflection of his business process. For an inventory problem, the stock-on-hand quantity is dynamic. The transaction history is ever-growing (we hope for his sake!) but each transaction stored in his history table is STATIC. Keeping a dynamic quantity in a static data set is not a good design. If we don't tell piet about such things, how will he ever find out?

The Doc Man puts it more eloquently than I would.

Perhaps I wasn't clear. The primary location for the current stock qty should be in the part table. A snapshot of the current stock qty is merely stored in the transaction table to see what the balance was at the time of the transaction. Setting up your data in this manner will allow you to track parts information history much better.

I think this dogmatic rule of not placing information in a field that has been mathematically derived is mis-understood and over used, much like normalizing a database. Taking every db to 5th normal form is just not needed. There is a time when common sense has to be used.

Say a bank has 100,000 customers, each with 500 transactions during the year. Do you mean to tell me that they are going to add and subtract 50 million transactions every time a customer does a deposit or withdrawal to see how much money they have. Nuts. You're tying up clock ticks, counting stuff over and over when you could be doing real work...

I do agree that when ever the calculation can be derived from within the record, or, using a constant, you should try not to store the value.

If you do want to fall back on data modeling, the qty of stock on hand is an primary attribute of the part entity. The qty of stock on hand is not an primary attribute of the transaction entity.

I guess it all depends on where you want to draw your line in the sand, where you store a value or not...

All of this, of course, is just my opinion - :)

thanks everyone for all your replies.

i am desperately trying very hard to understand what you all mean.

i never realized that such a simple and small db would be so complicated.

the db attached in my 1st post is my 1st PROPER db (or so i thought);
just when i thought i understand Tables and Relationships (my starting point), along comes all of you with your suggestions which honestly confused me BIG TIME ! i'm 36yrs old and only recently started with a db to help out a friend that runs a small Pest Control Supply store.

i guess i'll go back and start all over again and use some of your suggestions (not that i really know what you mean) and a2k's Help function doesn't help much either (at least not with my particular "calculation problem").

thanks anyway. you guys clearly know alot! about db's.

have a good day.
regards,
piet.

Say a bank has 100,000 customers, each with 500 transactions during the year. Do you mean to tell me that they are going to add and subtract 50 million transactions every time a customer does a deposit or withdrawal to see how much money they have. Nuts. You're tying up clock ticks, counting stuff over and over when you could be doing real work...What usually happens in a system like this is that the transactions have a sequence number and the balance is stored with the transaction. When the transactions are reported out, they are ordered by the sequence number so that the balance is correctly identified. This is not analogous to recording it in the parts table.

I'm not dogmatic about not storing values when they could be calculated, but I have been involved in lots of stores systems over the years (I am an accountant by trade) and storing the current balance in the parts table is simply a bad way of doing it. It causes all sorts of problems, especially if the system is multi user. I have seen systems where the balance is stored, say at the month end, and this is used to compute the current balance by adding on the current month transactions. That is OK because the balance calculation is run as part of a month end routine, and there's no one adding data at this time.

First, thanks for keeping this discussion amicable. I really don't mean to come across as antagonistic.

I still haven't heard a good argument to sway my opinion. I read things like 'simply a bad way of doing it' and 'It causes all sorts of problems'. But nothing concrete.

I can buy into the example where you say 'balance is stored with the transaction', even though I wouldn't do it that way. It does however, seem analogous, to me, to recording it in the parts table, especially when you consider that it breaks this celestial, calculated value rule.

Do you know of any other authorities (other than ourselves ), that have documented their arguments on this issue, in a book or white paper? They may have issues I haven't considered...

First, thanks for keeping this discussion amicable. I really don't mean to come across as antagonistic.Yeah, no worries, mate
I still haven't heard a good argument to sway my opinion. I read things like 'simply a bad way of doing it' and 'It causes all sorts of problems'. But nothing concrete.Here's a real problem that your suggestion produces:
- Say you have two users that want to update the same part, maybe one is receiving goods and one is issuing, at the same time. The current balance is read by both routines, and the new value then written back to the parts table. Regardless of which one writes first, the balance will be wrong because it will not include the other transaction.
Now that on its own is enough to make me avoid your method.I can buy into the example where you say 'balance is stored with the transaction', even though I wouldn't do it that way. It does however, seem analogous, to me, to recording it in the parts table, especially when you consider that it breaks this celestial, calculated value rule.I suppose being an accountant, I always want an audit trail. I want to know, not just what the balance is, but how we got to that balance. I also want to know what the balance was yesterday, or on 1 April 2003. This is a strong second reason for not using your method, but is addressed by the 'balance with transaction' approach.
I don't regard this as a celestial rule. As my profile says, I'm just a hacker. I've never had a lesson on db design in my life. My view is based on hard eveidence that there is a right way and a wrong way.
Do you know of any other authorities (other than ourselves ), that have documented their arguments on this issue, in a book or white paper? They may have issues I haven't considered...
Sorry, no. The next book I read on db design will be the first!

Consider the following scenario you posted:

'Here's a real problem that your suggestion produces:
- Say you have two users that want to update the same part, maybe one is receiving goods and one is issuing, at the same time. The current balance is read by both routines, and the new value then written back to the parts table. Regardless of which one writes first, the balance will be wrong because it will not include the other transaction.'

...in more detail but lets make it more complicated by having two users wanting to issue the same parts:

Calculated value Scenario:

1. User 1 looks up a part, say 'pump x'.
2. The database goes to the transaction table which has 10K total transactions.
3. It finds all 500 previous transactions for that part.
4. By adding and subtracting all of those transactions, it determines that there must be 5 pump x's in stock.
5. User 1 has all the fields completed to order the part and he plans to order all 5.
6. At the same time user 2 is at the exact same point and plans to order all 5.
(The database has had to go through steps 2-4 all over again)
7. One of the users has to hit enter first to process the transactions.
8. Let's say user 2 hit enter first. At that time the database would need to recount the transactions again to make sure there was still 5 in stock. (Steps 2-4)
9. The count comes back good and lets user 2 order the pumps.
10. Now user 1 hits enter and the database has to add and subtract all the transactions again. (Steps 2-4)
11. The count comes back bad (not enough to fill his request), and notifies user 1 that it cannot issue the parts because some one else beat him to the punch.

Note: The database had to do steps 2-4, four times.

Stored value scenario:

1. User 1 looks up a part, say 'pump x'.
2. The database looks up the stock qty in the part table and determines there must be 5 pump x's in stock.
3. User 1 has all the fields completed to order the part and he plans to order all 5.
4. At the same time user 2 is at the exact same point and plans to order all 5.
(The database has had to do step 2 all over again)
5. One of the users has to hit enter first to process the transaction.
6. Let's say user 2 hit enter first. At that time the database would need to
look up the stock qty in the part table and determines there must be 5 pump x's in stock.
7. The count comes back good and lets user 2 order the pumps.
8. The db updates the stock qty in the parts table and places a snapshot of the current stock qty in the new transaction record.
8. Now user 1 hits enter and the database would need to
look up the stock qty in the part table.
9. The count comes back bad (not enough to fill his request), and notifies user 1 that it cannot issue the parts because user 2 beat him to the punch.

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.

=========

I suppose being an accountant, I always want an audit trail.

From my earlier post:

'Perhaps I wasn't clear. The primary location for the current stock qty should be in the part table. A snapshot of the current stock qty is merely stored in the transaction table to see what the balance was at the time of the transaction. Setting up your data in this manner will allow you to track parts information history much better.

ken :)

Piet, I was trying to avoid this 'cause you said you were confused, but with some strong ideas around, I think I need to put on the "ol' perfesser" hat.

When you build a "real" database, one that is meant to attack a real-world problem, you should think kind of like this: You are building a MODEL of the problem in an exotic sort of way.

Each object in your database should somehow represent a real object in the problem. If the problem is running a business, then the objects should be objects (concepts) you use in your business, with some possible exceptions I'll try to mention later.

Fer instance, you have parts. OK, that means your model has to have parts. You put the parts into a parts table. Well, that was simple enough. Next, you have customers. Put them into a customer table. Still with me? (I've been watching too much Emeril Live on the Food Channel...) Got many suppliers? A suppliers table makes sense.

Now let's look at parts flow. You order some parts from a supplier. Well, there is the beginnings of a purchase-order table. But the details will be purchase-order line-items. Which is probably a child table of the PO table.

Your customer orders some parts. Well, there is your invoice table popping up out of nowhere. Again, it has details. Probably an invoice line-items table as a child of the invoice table.

You have a couple of other things to consider - like shrinkage (bad parts) and stock returns (customer changed his mind or you repossessed after the deadbeat didn't pay up or whatever...) These have to be factors in your model if they are factors in your business. Do you treat them like ordinary supplier records or invoice records, or do you have shrinkage tables and stock return tables separate from PO and invoice tables? Your call, your DB, your design. But it is a factor to consider.

Then there are the accounts payable and accounts receivable items, like when you pay for your purchase orders or GET paid for your invoices. EVERY ONE of the entries is an element of the real way you do business.

Your model has to define the business rules. Like, delinquency limits on invoices, potential for discount rates for bulk purchases, etc. Every rule will be either a query you run (to detect delinquency once per day) or a formula you use (to compute discount rates) ... every business rule corresponds to some element of your Access MODEL of your real business.

Now, here's the trick. If you are about to build an Access model of your business, you already need to have explored it on paper well enough to identify the things you are going to build in. Which means you will have a list of tables and what is in each. You will have a list of rules. You will have a list of relationships.

WHAT? Relationships? Yes... Invoices link to customers. Purchase Orders link to suppliers. Parts link to POs (through line items). Parts link to Invoices (through Invoice line times). Every time you find such a relationship, you have identified options for naming a KEY. Then your KEY can be used in place of the linked item in the base record. You would USE these linked items with JOIN queries.

The idea is that you must understand the business before you can build the model of it. So you start on paper and work towards the database.

Don't despair - all of this is eminently doable. Been done many times by many folks in many ways. It just isn't trivial if you want to do it right.

Good luck, Piet.

(Perfesser hat comes off.)

Ken, regarding your scenario of how often you have to run your query to find out how many pumps you've got...

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. There are other ways to do this, too. You mentioned it yourself. Put a manual lock on the part number when the first person comes in, saying "Joe is ordering pumps." If Mike comes right behind him to order pumps, he hits the LOCK and gets a message, "Inventory conflict - another clerk is ordering pumps."

And to be honest, locking records is more common than not in modern inventory DBs. So your first scenario wouldn't flow as you described it. Nor would your second.

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.

Also, there is another way to do this that you omitted. Begin a transaction in SQL-type operations. Write a TENTATIVE stock draw for the part WITHOUT attempting to check whether you have enough. Then run a query that LOCKS the transaction table for the duration of the operation. (I.e. whole table lock.) Now, finish the transaction.

Outside the transaction, see if the resultant parts count, including the TENTATIVE stock draw, remains positive. If so, convert the TENTATIVE stock draw to a REAL stock draw. If not, revoke it entirely. That is ONE append and one summation pass through the transaction table as an atomic operation, and one update or delete depending on the result. The update would KNOW the transaction number so it would NOT be a scan case. The APPEND would not be a scan case, either.

With table locking and BEGIN TRANSACTION, the first person puts in the TENTATIVE draw and does the query as a unit, non-interruptable. So the race you are talking about doesn't in fact exist. The second person cannot MAKE the tentative draw until the first person's transaction is complete.

'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?

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

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.

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! :)

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

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.

???

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.

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

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.

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.

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 ;)

'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?

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

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.

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.

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.

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

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!

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.

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?

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

kh

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

kh

KH, yes i still have plenty problems to overcome but Brianwarnock
have been a great help with suggestions that suites MY! needs.
(see /forums/showthread.php?t=75249)

Like i've said to Brian as well, I do understand the consequences of not calculating the 'current/available stock' value correctly, but remember that this is MY db, MY responsibility and only I will use the db for personal use.

One thing that I also only just now thought about is;

If I change the PRICE value in the ProductsTable, then all of the existing records gets re-calculated with the New price. This is NOT GOOD !
Because on 1/1/2005 I want to change update the ProductPrice with a new value that must apply only to records captured from 1/1/05 onwards.

Anyway, as the history of this thread will show, I still have alot of work to do... And I can only hope that that are more guys like Brianwarnock, who at least understands that I'm trying hard to do something that might seem very simple to most of the other guys here.

Any and all help/suggestions will be greatly appreciated.

Have a good day everyone.
Regards,
Piet.

If I change the PRICE value in the ProductsTable, then all of the existing records gets re-calculated with the New price. This is NOT GOOD !
Because on 1/1/2005 I want to change update the ProductPrice with a new value that must apply only to records captured from 1/1/05 onwards.
There are a few ways of solving this problem, but here I would recommend that you store the price in your transaction record. I know this seems to contradict what I've said before about not storing calculated values, but in this case it is the most practical.

The price per unit isn't a calculated figure, it's not a violation therefore ;)

There are two "purist" ways to look at this.

1. Store all prices in a separate table, foreign key = part ID, that is time-tagged twice. Once with the first date on which that date is valid, once with the last date on which that date is valid. Make your JOIN of price to part ID include a test of the date from your price history table.

2. As Rich points out, if you store the unit price in the line-item, there is no violation of normalization because the line-item either contains the date or is the child of an entry that contains the date. So you would have the unit price stored in a table whose keys (directly or indirectly) are date and part ID. STRICTLY SPEAKING, the date should be part of the line-item just 'cause there is a normalization rule dealing with this situation - but in practical terms the date is unequivocal 'cause the purchase order date IS stored in the parent record.

The normalization rule in question says something to the effect that you can't/shouldn't store something in a table if it doesn't depend on all of the keys of the table. Technically, unit price on a given date doesn't depend on the sales invoice number, so this is a nit-picking violation.

Now, here is a REAL wrinkle, but it makes sense in this discussion. If you have the ability to charge a discount for any reason (bulk purchase, preferred customer, special promotion, inventory clearance, you name it), then the unit-price really DOES belong in the line-item 'cause the date is no longer the only key that determines the unit price. I.e. your price table would have a bunch of extra codes (and records) in it to cover the various discounts. BUT the long-term easy way out is to just store the applicable price for that one sale of that one line-item in the line-item table. Compute the effective unit price from some other method, then STORE it. Never again do you have to remember what you charged on that day - or why! (You could, of course, include a field that showed a code for discounts... but you technically don't need that if your business model doesn't need it.)

So in other words, it's a good idea to hold the applicable unit price in the line item entry. But as you know, I go into professor mode sometimes. Since you have expressed confusion on this topic in the past, I wanted you to understand more about WHY it was a good idea.

Piet, Just curious, are you going to store the value or calculate it when you need it?

kh

Calculate it, if he wants to maintain data integrity that is;)