Warehouse inventory and sites

[Deke Hikari]

Hello everyone.

I'm building my database at the moment but I seem to mix things up. What I would love to see is the structure of my database (I'm struggling with that one :s) and I can take it from there.

Situation: my company has a warehouse where the goods (mostly traffic signs) are stored. We deliver our goods to different temporary work sites. When the sites don't need the goods anymore they send it back to the warehouse.

What I need in that structure:

1. Orders (reserve goods without modification on the quantity on hand) and deliveries (modifies the quantity on hand) from and to the worksites.
2. Stock modifications like inventories (broken, stolen, ...)
3. Orders to and deliveries from Suppliers

In the future there will be a renting system added to all this: a traffic sign will cost x€ for each day the worksite had it on site.

This is what I made so far but I think I'm doing it wrong.

Thanks a lot in advance and should you have any quastions do not hesitate to let me know ;-)


Greets,
David.

 

[GaP42]

Your structure includes employees and other tables that are not in scope of the extension you require. The following is a preliminary structure for discussion and review. A discussion of inventory and stocktaking with sample access code/ db is available here:
http://allenbrowne.com/AppInventory.html
Not that stock in hand is calculated and not stored.

Re the preliminary structure here:

Work Order and Customer Order are likely to be merged into one table, as are Customer and Supplier, each distinguished by type fields. The diagram helps to clearly show the two processes.

If your tbl_Articles is = Stock items, then if a stock item is specifically identifiable then you need OrderItems1-N to Stock Items to specifically identify the items that are issued to the site (not just the quantity) (although for some items you may only track the quantity). A similar requirement may exist if you acquisition process is to add specific items to inventory (stock items). If you do not track the items at this level of detail then Orderitem and SupplierOrderitem are directly associated with Stockitem.

The above assumes a Work Order may only include items for one site.

if Work sites are to be charged for use of items issued from stock then are they managed by your customers? or this a mechanism you are using to improve efficient stock management within your organisation?

Items in Use will need to track to date issued date returned. You Articles table appears to have a price. Is this the price charged to the site or the price paid to acquire the item?

Transportation, Employees, and Stock Categories are not considered or examined in this simple overview, however you have them in your structure.

 

[Uncle Gizmo]

I thought that the Northwind sample database might be adapted to your use... It's already has inventory control built into it!

So I asked chat GPT what it thought. Chat GPT can give you misleading results so you will have to verify it yourself!

Chatty said:-

The Northwind 2.0 Developer Edition, recently updated by a dedicated group of Microsoft Access developers, offers a robust framework that can be adapted to your warehouse and worksite inventory needs. This version emphasizes advanced features, including enhanced VBA for stock control, making it a suitable foundation for your project.

Key Features of Northwind 2.0 Developer Edition:

Advanced Inventory Management: The template includes comprehensive inventory functionalities, such as tracking stock levels, managing purchase orders, and handling stock allocations. These features can be customized to align with your specific requirements.

Enhanced VBA Integration: Northwind 2.0 utilizes professionally written VBA code to enforce complex business rules and processes, providing a solid base for further customization.

Adapting Northwind 2.0 to Your Needs:

1. Worksites Management: Introduce a new table to represent worksites, establishing relationships with existing tables to track inventory movements to and from these locations.

2. Stock Transactions: Leverage the existing inventory management features to log stock movements, including deliveries, returns, and adjustments, ensuring accurate tracking of stock levels.

3. Rental System Integration: Develop additional modules to handle the rental aspects, incorporating fields for rental duration and costs, and integrating them with the existing order and inventory systems.

Getting Started:

Accessing the Template: Open Microsoft Access (2019 or later), navigate to File > New, and search for "Northwind" to find and create a new database based on the Northwind 2.0 Developer Edition template.

Exploring Features: Utilize the built-in tutorials and documentation to understand the existing functionalities and identify areas for customization.

By building upon the Northwind 2.0 Developer Edition, you can efficiently develop a tailored solution that meets your company's specific warehouse and worksite inventory management needs.

 

[The_Doc_Man]

@Deke Hikari - just as a comment, you have a bad structure option in your design that won't kill your project - but it will lead to some really awkward moments because some queries will suddenly become READ-ONLY (I.e. could not update through the queries).

In the diagram attached in your first post of this thread, you show tables Commandes, Chantiers, and Employes. You have relationships shown directly between Commandes and Chantiers, but you also have an indirect relationship between those two tables that goes through Employes.

Imagine a query that has to include data from Detail_Commandes and Chantiers in the same record. If you have such a query, even if it doesn't actually name something from Employes, Access will see what is essentially an ambiguous path. That is, when it is analyzing the tables to set up what is called a "query plan" (something we don't see unless we ask for it), it won't know how to properly set up this plan. It makes no assumptions about which way is desirable. And that complicates query analysis.

Employes can depend on one of those tables but not on both. As it is shown, if either of those relationships is one-to-many then you could also get duplicate rows if you don't constrain the "many" side with a WHERE clause of some kind.

 

[GPGeorge]

I thought that the Northwind sample database might be adapted to your use... It's already has inventory control built into it!

So I asked chat GPT what it thought. Chat GPT can give you misleading results so you will have to verify it yourself!

Chatty said:-

The Northwind 2.0 Developer Edition, recently updated by a dedicated group of Microsoft Access developers, offers a robust framework that can be adapted to your warehouse and worksite inventory needs. This version emphasizes advanced features, including enhanced VBA for stock control, making it a suitable foundation for your project.

Key Features of Northwind 2.0 Developer Edition:

Advanced Inventory Management: The template includes comprehensive inventory functionalities, such as tracking stock levels, managing purchase orders, and handling stock allocations. These features can be customized to align with your specific requirements.

Spoiler:

Enhanced VBA Integration: Northwind 2.0 utilizes professionally written VBA code to enforce complex business rules and processes, providing a solid base for further customization.

Adapting Northwind 2.0 to Your Needs:

1. Worksites Management: Introduce a new table to represent worksites, establishing relationships with existing tables to track inventory movements to and from these locations.

2. Stock Transactions: Leverage the existing inventory management features to log stock movements, including deliveries, returns, and adjustments, ensuring accurate tracking of stock levels.

3. Rental System Integration: Develop additional modules to handle the rental aspects, incorporating fields for rental duration and costs, and integrating them with the existing order and inventory systems.

Getting Started:

Accessing the Template: Open Microsoft Access (2019 or later), navigate to File > New, and search for "Northwind" to find and create a new database based on the Northwind 2.0 Developer Edition template.

Exploring Features: Utilize the built-in tutorials and documentation to understand the existing functionalities and identify areas for customization.

By building upon the Northwind 2.0 Developer Edition, you can efficiently develop a tailored solution that meets your company's specific warehouse and worksite inventory management needs.

LOL, Chatty got it mostly right. I recognize a lot of that text, which comes directly from blogs and announcements about the NW2 templates. I might even have contributed to some of it. Tom vS did write some of it for sure.

To make a long story short, and appropriate to your requirement, inventory control is often more complex than we implemented in NW2 Developer. The actual shipped design in NW2 is complex enough, we felt, to be a good starting point for learning about Access without being so comprehensive it would be overwhelming for new developers to use.

For example, we did not include the ability to reserve products for future orders. An order is considered final when it's paid for and shipped. Your reservation step adds more detail to be tracked, so you may need to tweak the NW2 model if you decide to use it.

With regard to calculating Stock On Hand, you might want to look into how we incorporated periodic stock-takes to avoid having to run queries that calculate transactions over the entire lifetime of the application. True, SoH should be calculated in a query, not stored in a field in a table. But periodic stock takes allow you to run those calculations over the most recent period following that stock take.