Stock-take

[Cullen1109]

Hello, new to the forums here and I was just wondering if anyone could offer a small piece of advice in how to create a stock-take section of an inventory management system.



The system follows all normalisation rules so has no calculated values, except in queries, and hence so only calculates the stock on hand when needed through a transactions table.

This has proved perfect, except for when the company then asked me to add a stock-take section, which would go as usual, but then show any errors such as shrinkage.

Of course if say a "SMART Programmer" product was at -1 yet they had 3 in stock, this would be a problem.

Not wanting to resort to calculated values being stored is there any way of resetting the transaction values to a set one? I have tried many different update queries, and new records in the transactions being added for the difference between the values, none have worked.

Can anyone help?
Thank you in advance.
Michael.

[AndreQ1]

You'd have to provide a lot more information here, but from what I can tell, you're trying to reconcile the calculated stock vs. the actual stock.

If stock values need to be reset, based on actual values, you could create a new category called Adjustment that would behave like a sale or restock inventory.

[Cullen1109]

Hiya,
That's exactly what I am trying to do, but in the processing side of things, not on the front end.

I tried the table adjustment that would behave like a sale, adding each product in, with a select calculation to create stock on hand, compared with the products that were measured within the stock-take, but this proved to be overly complex and did not work.

A break down of the system is:
The accountant makes an order of 20 screws and 20 nuts (for example)
The order is given a purchase order ID, and each transaction is given a transaction ID within that. They are then designated a transaction type ID, incoming or out going.

So now within the stock we have 20 screws and 20 nuts.

But when the weekly stock take comes along, they count 19 screws and 20 nuts. This would need to be amended to 19 screws within the system, and a report of shrinkage shown, i.e. 1 screw is missing.

So I was just wondering is there a simplified way of creating this shrinkage report and discovering the difference between the current total and the entered total, and then amending the totals. Of course this would be a BIG calculation, but hopefully someone can just point me in the right direction?

Thank you again!
Michael.

Edit:
I've worked out something, which is to create the total of 'Units In Stock' as I would for the stock levels, then created a transaction which subtracts this total (i.e. classes the transaction as an outgoing, hence removes the stock) and then recreates a transaction on the same stock ID, for incoming with the entered total.

Sorry if that's hard to read, things always make more sense in your head

[evander]

Is this thread already solved?

I wonder how you designed your database.
I'm currently implementing an inventory system myself, and it consists of:

1. Purchase receiving (transaction)
2. Sales (transaction)
3. Adjustments for quantity and cost (transaction)
4. Branch transfer (transaction)
5. Branch request (transaction)
6. Purchase order (transaction)
7. Pending requests (report, monitoring)
8. Pending orders (report, monitoring)
9. Ending inventory per branch, or consolidated (report, monitoring)

I structured the database so it becomes very easy to obtain the inventory quantity and cost balance at any given report date.

An inventory system should have at least the following fields in any of its transaction tables:

1. Document ID
2. Document Date
3. Inventory ID
4. Quantity
5. Cost

Any missing field will make it difficult to complete an inventory database.
You can keep in touch with me for more information.

PS: I'm an accountant by profession.

-jan-

[Cullen1109]

Thanks for the reply, but I'm past that part.
I'm working on a calculation to create a stock adjustment after all transactions have been processed.
I already have all that within the Inventory-Transactions, and Quantity should not be calculated within, but calculated when needed.

Thanks,
Michael.

[NoellaG]

Hi,

I worked with a lot of professional warehouse systems from home made to SAP, and they all have in common that they use next to the purchase order and delivery order tables, a separate movements table and a (yes, calculated) stock table with shows the current stock situation: what is exactly stored where.
The purchase/order tables reflect the documents you get from the customer (in a lot of cases these are filled by a transmitted XML doc); the movement tables are filled the moment the stock really comes in or leaves the warehouse (registrated by scanning the items), but they also can contain movements as repacking, move to another stocklocation, stockcorrections, ...
The stocktable is automatically updated, mostly by triggers, but always gives the current situation. A lot of warehouse programs also take a stockpicture (snapshot) at the end of the month.

Hope this gives you an idea

greetings
NG

[evander]

What does "calculated within" mean?
I do calculate ending inventory based entirely on queries. I don't store calculated values anywhere in the database.

Is this what you wanna do?
1. You have to calculate a tentative ending inventory that still excludes physical count adjustments. We'll call this "system count."
2. Your client conducts a stock take and prepares a count sheet. You need to account for this data in your database. We'll call this "physical count."
3. You need a form that displays the system count as of some report date (but should coincide with the date of the physical count).
4. You need another column with blank fields placed beside the system count where the encoder can input the actual physical count.
5. Last, another column calculates the adjustment quantity.

I believe you can achieve this if we have a similar design in mind. Otherwise, it can really be difficult for you.

[Cullen1109]

I meant 'Calculated within' as using Update Queries to keep a quantity. Instead it is based on the factors described above.

I calculate the stock on hand using queries only, as described above earlier.
However when a stock take happens, sometimes there can be anomalies, which need to be physically changed, hence so there needs to be a calculation in a query to change the transaction value of the stock on hand.

Thanks,
Michael.

[evander]

Michael, you said, "hence so there needs to be a calculation in a query to change the transaction value of the stock on hand."

You can't change the value of the stock on hand because it is a calculated value. It is equal to Purchases - Sales +- Adjustments.

What you need to do is to create an adjustment, so that the "stock on hand" recalculates to match the physical count.

If you find this a workable solution, I can send you a simple database that accounts for quantities only: purchases, sales, and adjustments.

Cheers,
Janjan

[Cullen1109]

Sorry it has taken me a while to get back, been ill.

I know that second sentence, that is how the stock is being calculated. The adjustment table, how would you go about it? That is what I am trying to figure out, the recalculation to match the physical count, but I'm struggling to find a way. I've tried lots of different ways, but to no avail.

[NoellaG]

Hi,

hereby included you'll find a set jpg's with some of the main diagrams of an existing warehouse system that's operating a refrigeration warehouse in the Antwerp harbour. The system is working without problems for quite some years now. It handles some hundreds of movements a day, and on a normal day it has +/- 10 simultane users.

grNG

[evander]

Here's a sample. Hope you can make use of it.

Jan

[evander]

By the way, if you want, you can add to the database the capability to derive adjustment quantities by:

(1) In the tbmInventory master list table, create a field "Actual Count"
(2) In the same tbmInventory master list table, create a field "Count Adjustment"
(3) These two additional fields are temporary fields for quantities to be appended in the tbdAdjustment transaction table
(4) Include these fields in the query and form for frhInventory
(5) In the frhInventory form, calculate the adjustment quantity using VBA code
Count Adjustment = Ending Quantity - Actual Count
(6) Create an append query that appends data both to tbhAdjustment and tbdAdjustment.
(7) Set the temporary fields to Zero.

Hope this helps.
Good luck.