Transaction table

[L'apprentis]

Hi,
I don't want to abuse anybody's patience but I have yet a couple of other quick question about transaction table.
I am using a summation query for the stock on hand where by keeping it simple: stock = sum of movement in the table. What hapens if a simple function is used to calculate the stock. ie: Stock=[Qty Declared-(Qty Declared-Qty Received)-Rejects]-Qty Allocated

All those Qty belong to the same field then I can't just run a sum.

My other concern is that:
Qty Declared
Qty Received
Qty Rejected

are three values entered at the same time but in the same (transaction type) field so data like the date would need to be entered 3 times for each item received?

Thank you very much for your time and patience again,

 

[The_Doc_Man]

Using either transaction types in a single transaction table or a UNION query on several different tables, you still end up with the simple idea that

amount of X on hand = Sum( all transactions on object X )

You have going in that direction, which is good. You might still have too many types of transaction - or an undesirable type - in your list, though. I will toss an idea your way. You decide if it is right, wrong, or indifferent.

For any given inventory item X, you need several types of transaction to be rigorously correct.

1. Stock add (by completed purchase order from vendor) - this includes items appearing for the first time because they are new to your product line.
2. Stock draw (by completed purchase oder to customer)
3. Stock adjust down (breakage/pilferage/retirement) - this includes what you do when you stop selling item X and remove it from your shelves so that you will never order or sell any more.)
4. Stock adjust up (return to stock after original valid draw)
5. Stock on hand at archiving time. There will be no such record for new X during the period between the time this new X is added to inventory and the next archiving step. However, after the first archiving step, EVERY item for all X will have such an entry.

It sounds to me like your "Declared" and my #5 are similar. My view of #5 is this: You can track records forever, but your DB cannot hold an infinite amount. So what you do is pick an interval, perhaps quarterly. Perhaps monthly. Other times are OK too. Whatever floats your boat. You clean out the old stuff. You keep the relevant stuff.

You MUST include dates on all transactions including the Stock On Hand record. Times are optional but could also be useful. If so, use a Date field rather than a text date. So now you write a couple of queries. As a parameter to the queries, choose a date that will be your archiving cutoff or split point. Usually the last day of some month or quarter. Doesn't matter as long as you are consistent about it. The technical cutoff will be, in effect, 23:59:59 of the chosen date. The first record to be KEPT will be 00:00:00 of the next day in sequence.

Query #1 - Compute stock on hand as of your cutoff date. Create a single stock-on-hand transaction with the cutoff date. Put it in a temporary table until you need it. This will be a two-layer query. I.e. a query of a query.

Query #1A - a summation query grouped by item number for all transactions earlier than the archiving date so you can get the "inventory of X at archiving date"

Query #1B - an append query that draws from the summation query and feeds it to the temp table. The stock-on-hand record will have the archiving date

Query #2 - Append all active transactions with date less or equal to the selected cutoff date. Assuming 1A and 1B worked, this does NOT need grouping or aggregated functions.

Query #3 - Delete all active transactions, same criteria. Again, only needs the date criteria.

Query #4 - Append the Stock-on-hand transactions you kept earlier from query #1. Needs no criteria, they were all applied earlier.

Query #5. Empty out the temp table that held stock-on-hand transactions. Needs no criteria.

So now, once you have archived, your inventory amount is simply the stock-on-hand transaction (that represents all transactions prior to the cutoff date) plus the sum of all subsequent actions. This way, you keep the size of the table down so searching isn't such a bear. If that is what you meant by a "Declared" transaction, then we are on the same page. Maybe.

How you handle the contents of your archive table becomes a point of company policy. You can surely export the archive table to a comma-delimited file and just keep around the files. Put them on your favorite backup media. Or if your transactions are not that critical, you can round-file them after some time limit. That will depend on company policies.

 

[L'apprentis]

Thank you very much Doc man I am really gratefull.
This will be really helpfull and I am gonna try to see if I can use such a system to my needs or maybe adapt it in the way I want. However, What i meant by the quantity declared was the quantity declared on the Supplier delivery note; you see we are receiving castings in large amounts and each purchase order is delivered in several deliveries. At each delivery we got the quantity we put in stock (quantity rejected), a quantity that we can't accept in stock because of poor quality (quantity rejected) and the quantity that the supplier counted (Quantity declared).
Rejects are sent back and quantity declared is tightened up with accounts.
I could simply put the the quantity declared in the transaction table but as it has been mentioned in many discussion and once again in the above post: Item transaction should be rigorously recorded. That's the reason I thought of including
Quantity declared.
For now I am gonna look a bit more in depht on the Doc man last proposition for the calculation of stock.

 

[The_Doc_Man]

What i meant by the quantity declared was the quantity declared on the Supplier delivery note

By itself, this could be either a meaningless entry or a redundant number. See next comments to understand why. And notice COULD - not IS.

quantity that we can't accept in stock because of poor quality

This amount rejected for quality defects, plus the above quoted quantity TOGETHER can become the Stock Add plus Stock Shrinkage elements such as I described. If you have declared, accepted, and rejected (your terms), ONE of those three is redundant for INVENTORY purposes - though not necessarily for billing purposes. But maybe even then... The reason is that mathematically, if I got your comments figured out,

Declared = accepted + rejected

i.e. One of these three is NOT an independent quantity. You never store dependent quantities if you can re-compute them via simple query.

Now, at this point, you have the number of X in the box (declared) and your quality control folks determine the proportion of accepted vs. rejected. For billing issues vs. your supplier, you of course want to know the reject rate. It is your call as to whether you enter your "declared" as a Stock Add and immediately enter your "rejected" as a Stock Adjust Down. Or whether you track the rejects separately in another table and only store the "accepted" items in the main inventory table. Without knowing a LOT more (including more than I care to know) about your DB, I don't know which is right for you.

If you stored your "declared" and "rejected" as TWO transactions (with opposite effective signs), what is left is automagically the amount you have called "accepted" because of that dependent mathematical relationship. If the transactions contain enough information to tie them back to an original purchase order, that gives you enough to do your tracking for long-term quality control purposes. In other words, store one less transaction amount. You would, in the long run, save approximately 33% of the space required to track incoming shipments by eliminating those things bound by a mathematical dependency relation.

Now, the advice ...

Item transaction should be rigorously recorded

absolutely IS good advice - but rigorous recording does not mean "tracking superfluous data that could be regenerated via query" - it DOES mean tracking ENOUGH data to be able to recreate all the numbers based on any mathematical relationships such as I demonstrated above.

It is a lot to think about, but the result can become a good thing.

Also, side note: It is too late to edit my previous post, so make note of the following...

Query #1A - a summation query grouped by item number for all transactions earlier than the archiving date so you can get the "inventory of X at archiving date"

That should be "earlier than or equal to" the archiving date because all of the other queries allowed "equal to" as well.