calculation problem

[piet123]

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.

 

[maxmangion]

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

 

[maxmangion]

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!

 

[piet123]

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.

 

[neileg]

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.

 

[The_Doc_Man]

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.

 

[KenHigg]

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.

 

[piet123]

calculation problem continues (or not)

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.

 

[neileg]

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.

 

[KenHigg]

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.

 

[The_Doc_Man]

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?

 

[neileg]

The Doc Man puts it more eloquently than I would.

 

[KenHigg]

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 -

 

[piet123]

such a small db, yet so complicated

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.

 

[neileg]

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.

 

[KenHigg]

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

 

[neileg]

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!

 

[KenHigg]

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

 

[The_Doc_Man]

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

 

[The_Doc_Man]

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.