Warehouse Inventory Project

[ahmad_rmh]

Dear Experts,

I have made a QOH value in inventory adjustment form (dlookup function) to see how much QOH for inventory adjustment.

But the issue is that QOH is dynamically changing as the QOH gets changed.

How to make static as the record saved?

 

[CJ_London]

For the benefit of others for background- this is a continuation of this thread

Solved - Physical Inventory Adjustment

it won't because the recordsource to the form is too complex. Your method is not the right way to go and I don't want to spend time trying to get round the problems of a poor design. Best I can suggest is look into using a dlookup or dsum as a controlsource to a control

www.access-programmers.co.uk

 

[arnelgp]

But the issue is that QOH is dynamically changing as the QOH gets changed.

talk to your Accounting people how they do it or to the Warehouse manager.
i believe they are already implementing it while you are still figuring it out.

 

[ahmad_rmh]

Nothing, kindly suggest your expertise

 

[CJ_London]

you've ignored our expertise up to now, so I'm going to drop out

 

[ahmad_rmh]

Before dropping me, I want to share the theme of the project then it's up to you.

 

[Minty]

I've built 4 or 5 inventory systems in recent years. Some are more complex than others. They are not simple.
Trust us when we tell you, you have built yours' incorrectly.

This is why the only way you could make it work was with a fudge (the dsum).
Make yourself a proper transaction single table and do it properly as you will find you are making more and more kludges to make your existing model work, badly!

 

[ahmad_rmh]

See the attached files then suggest. Make delete of queries. I want the QOH and inventory adjustment.

Now the theme is in front of you. Suggest me as per the requirement. I will make corrections as what I could do my best.

 

[CJ_London]

see post #15 in your previous thread.

By way of note, I used that basic structure (in all 5 tables - locations, products, transaction types, transaction header, transactions) for a client that had 4 regional warehouses, around 600 retail branches and 5,000 stock movements a day and was required to track stock at all locations and in transit. There were more tables around suppliers, customers and retail sales, method of movement (own transport, courier, etc), product pricing but outside the scope of your requirement.

And I'll say again - you do not need to store the current stock, you calculate as and when required

 

[arnelgp]

see this one, as i have told you Involved your inventory quantity.
and you don't add/minus any quantity (adjustment) from the balance when you do the
physical inventory:
Inventory - Raw Materials, Work-In-Progress, and Finished Goods (corporatefinanceinstitute.com)

 

[ahmad_rmh]

see post #15 in your previous thread.

By way of note, I used that basic structure (in all 5 tables - locations, products, transaction types, transaction header, transactions) for a client that had 4 regional warehouses, around 600 retail branches and 5,000 stock movements a day and was required to track stock at all locations and in transit. There were more tables around suppliers, customers and retail sales, method of movement (own transport, courier, etc), product pricing but outside the scope of your requirement.

And I'll say again - you do not need to store the current stock, you calculate as and when required

The Main warehouse is sending monthly closing balance report to head office and stock movements of branches report.

 

[CJ_London]

so you calculate those as required

 

[gemma-the-husky]

@Ahmad

What is your role in your enterprise? Who is guiding your analysis and design?
Was a system specification agreed before you started, or are you just trying to add facilities that you think may be needed.
Who is actually managing the project? Do you have targets to meet and so on?

I ask this in the light of your last 2 posts, Nos 8 and 11 in this thread.

 

[ahmad_rmh]

@Ahmad

What is your role in your enterprise? Who is guiding your analysis and design?
Was a system specification agreed before you started, or are you just trying to add facilities that you think may be needed.
Who is actually managing the project? Do you have targets to meet and so on?

I ask this in the light of your last 2 posts, Nos 8 and 11 in this thread.

Is it concerned with that?

 

[gemma-the-husky]

Is it concerned with that?

Well, in some ways, because someone in your company ought to have a good idea of precisely what is needed, and how to do it. This shouldn't be something you have to puzzle out for yourself, if you aren't sure about how to achieve it. That's what I was thinking.

 

[CJ_London]

See the basic inventory thread at the bottom of this one

 

[ahmad_rmh]

Structure has been changed as suggested.

Kindly review the database file and suggest about inventory adjustment tables.

 

[CJ_London]

some improvement - still have multiple transaction tables though. Compare ReceiptSub with StockTransferSub and ReceiptMain with StrockTransferMain and ReceiptStatus with TransferStatus- they are basically the same. Only real difference is Vendors and Warehouses and even they can be combined if you included a type field. Similarly AdjustmentMain is similar to ReceiptMain with StrockTransferMain, just need to leave a few fields blank.

Appreciate some field renaming required 'transactionDate' rather tham ReceiptDate, TransferDate and AdjustmentDate for example

Don't understand why the category table is relevant to a warehouse.

In receiptMain, I would have thought you would want to be storing the vendorlocationID, not the vendorID - you can look up the vendor from the vendor table

 

[ahmad_rmh]

Hi, CJ, How are you, I hope so you are doing well...

I have to tried to streamline the database as per your advice.

Kindly check the database as attached and do me a favour to make a running sum on Union Query and Crosstab Query. I have tried but in vain. I would be very thankful for your kind favour.

Thanks for your opinions and suggestions.

 

[CJ_London]

OK - I've rebuilt your data with the following relationships

I've changed names to something I am more comfortable with but basically trantypes is your status, items and categories are unchanged. If you look at the table design, you will see some comments I've added around the fields in tblTransactions and tblEntities. In particular take note of the right joins between transactions to entities and entities to entity types and locations. This allows for where no entity is specified (an adjustment for example) and for the different types of entity (vendors and warehouses). The important thing to know is you can maintain referential integrity if the foreign (FK) field is left as null, referential integrity only applies when there is a value.

When you come to do your forms, you just need some simple rules to manage what is entered - I don't know which way round they will work but say you choose a transaction type first - you can then limit the list of entities to choose from based on that selection and the entity type. - you could add a joining table between entity types and transaction types if you wanted

In the attached db, I've populated the above with your existing data. I found just one inconsistency. In receiptMain you have items (id=7) going to warehouse 2 - this does not fit with your requirement to only measure the ins and outs of the main warehouse so I assume it is an error. If not and you are actually measuring the ins and outs of all your warehouses you will need to add in an additional EntityFK to tblTransactions (called say PrimaryFK) and also linked to tblEntities as you did with warehouses from your stocktransfermain.

I've also created a few queries prefixed with 'qry' so you can see how the tables relate

Your existing data is unchanged