Solved Need some advice for the design of Products table

[CJ_London]

Is this structure what you have in mind?

I think of this the other way visually so I would do your joins slightly differently

sales? orders>orderlines>products>assemblies>products_1

Products would link to assemblies on productPK to kitFK with a left join (since not all products have an assembly)
the _1 is the same products table, just aliased to appear a second time

to determine the full list of products for an order you can use the nz function

nz(product_1.productPK, productPK)

Just modified your database
The relationships now look like this (I've changed the name of KitFK)

because of the left join, you need an aliased query, kept separately here

qryAssemblyProducts

SELECT *
FROM tblProducts INNER JOIN tblAssemblies ON tblProducts.ProductPK = tblAssemblies.ProductFK;

To list all products required for an order you then have this query
qryRequired

SELECT tblOrders.OrderPK, tblOrders.CustomerPK, tblProducts.ProductPK, tblProducts.Product, tblOrderDetails.OrderDetail_ProductCount, qryAssemblyProducts.UsedCount,
">>>" AS spacer, Nz([qryAssemblyProducts].[ProductPK],[tblProducts].[productPK]) AS ReqProductPK, Nz([qryAssemblyProducts].[Product],[tblProducts].[product]) AS ReqProduct, Nz([qryAssemblyProducts].[usedcount],1)*[OrderDetail_ProductCount] AS ReqQty

FROM (tblProducts INNER JOIN (tblOrders INNER JOIN tblOrderDetails ON tblOrders.OrderPK = tblOrderDetails.OrderFK) ON tblProducts.ProductPK = tblOrderDetails.OrderDetail_ProductFK) LEFT JOIN qryAssemblyProducts ON tblProducts.ProductPK = qryAssemblyProducts.ParentProductFK;

The spacer column is just to separate data from the calculations

I added an assembly item to your order 2 to demonstrate and attached your modified db

 

[KitaYama]

sales? orders>orderlines>products>assemblies>products_1

By orderlines do you mean what I have as tblOrderDetails?
For now I don't have any intention to add sales before I can understand the products assemblies and orders' tables structure.

thank you.

 

[gemma-the-husky]

I really don't understand what you're trying to tell me. The situation is what I explained in my first post. Just as you can go and buy a whole car, or you can go and buy a bumper or a door after you have an accident. It means the manufacturer not only sells a whole assembled car, they also sell the parts.
If it's not what you mean, please rephrase it somehow I can understand what you mean.


1- We don't have any option for our products. So all above situation is irrelevant to our situation. We're not selling furniture or something of the sort to have any option, color etc. We are talking about industry and there's not two color of the same thing or two type of the same thing. At least for us it's the way it is. We have thousands of products, each one has its purpose until a new version of the same part is designed. So (for us) there's no situation where customerA wants it with golden bracket but customerB wants it in silver.
We have drawings, and very strict manuals to follow for each product. So there won't be a "Make it easy for the staff to pick the items" case.

Let me give you a clearer perspective. (I will delete the following images as soon as I'm sure you've seen them. They will fire me if anybody sees them on a forum)
View attachment 104862

View attachment 104863

These are two products. We receive an order for any of them, or at some point any single part within them. As you see in the right table, there's a productNo associated with each part used in this product (assembly). We receive an order for the whole thing or sometimes any of single products used in it (listed at left).

2- Prices and selling will not be managed in this database. The only thing I care is to have a normalized table to manage the incoming orders.

I won't try to break down your post which I have quoted.

Here are some general musings

Even though you don't put finished goods into stock, I am sure you don't sell finished stock as multiple parts. Unless you make these products in a single build, I expect you make subassemblies and then incorporate subassemblies in a build. Maybe a particular subassembly can be included in more than one product. A certain motor say be built into more than one product. Do you really not have a stock of ready built and tested motors, or anything else to build into a final product. Maybe not, but it still doesn't invalidate it as a logical analysis.

So if you have a product X that is composed of a complex arrangement of parts, it still might be better to notionally consider the whole consisting of parts and subassemblies, themselves composed of parts, that you notionally build into a finished product. eg In the breakdown below item 1 is used in the final build plus in the construction of some of the sub-assemblies. Once you allocate a part to be built into product X presumable you need to allocate it in your stock system so that another manufacturing request cannot take your part. Maybe you pick every part, put it on a large tray, and assemble it there and then. Maybe you make some of the sub-assemblies first. Even if you don't actually put stock the finished products into stock, it still might logically make sense to put the finished item and the sub-assemblies into notional stock, so you can sell a finished product rather than sell many different parts. Maybe you decide to make some common sub-assemblies and hold them ready assembled to save time. Even if you don't do that now, you decide to do it in the future.

the diagrams we never saw look like they have sort of sub assembly units. @CJ_London just noted there might be screws, fuses, and so on that are common to multiple products. Even if they aren't they might at some time, and it would make sense to build a system that works in that eentuality.

It would surely be easier to have your production system turn 1000 parts into a single part X, put part X into notional stock, and then sell it as a finished product. That might make your management system easier.

Product X

Item 1 x 2
Item 13 x 1
Item 28 x 6
Item 37 x 4
Item 65 x 9

assembly 4 x 2
Item 1 x 12
Item 2 x 6
Item 8 x 14
Item 24 x 16
Item 132 x 3

assembly 12 x 1
Item 1 x 6
Item 3 x 6
Item 19 x 14
Item 17 x 16
Item 48 x 3

 

[KitaYama]

I won't try to break down your post which I have quoted.

Here are some general musings

Even though you don't put finished goods into stock, I am sure you don't sell finished stock as multiple parts. Unless you make these products in a single build, I expect you make subassemblies and then incorporate subassemblies in a build. Maybe a particular subassembly can be included in more than one product. A certain motor say be built into more than one product. Do you really not have a stock of ready built and tested motors, or anything else to build into a final product. Maybe not, but it still doesn't invalidate it as a logical analysis.

So if you have a product X that is composed of a complex arrangement of parts, it still might be better to notionally consider the whole consisting of parts and subassemblies, themselves composed of parts, that you notionally build into a finished product. eg In the breakdown below item 1 is used in the final build plus in the construction of some of the sub-assemblies. Once you allocate a part to be built into product X presumable you need to allocate it in your stock system so that another manufacturing request cannot take your part. Maybe you pick every part, put it on a large tray, and assemble it there and then. Maybe you make some of the sub-assemblies first. Even if you don't actually put stock the finished products into stock, it still might logically make sense to put the finished item and the sub-assemblies into notional stock, so you can sell a finished product rather than sell many different parts. Maybe you decide to make some common sub-assemblies and hold them ready assembled to save time. Even if you don't do that now, you decide to do it in the future.

It would surely be easier to have your production system turn 1000 parts into a single part X, put part X into notional stock, and then sell it as a finished product. That might make your management system easier.

Product X

Item 1 x 2
Item 13 x 1
Item 28 x 6
Item 37 x 4
Item 65 x 9

assembly 4 x 2
Item 1 x 12
Item 2 x 6
Item 8 x 14
Item 24 x 16
Item 132 x 3

assembly 12 x 1
Item 1 x 6
Item 3 x 6
Item 19 x 14
Item 17 x 16
Item 48 x 3

@gemma-the-husky Thanks for your interest and lengthy explained post. I have to read it several time to be sure I've digested what I'm told. I'll reply then.

thanks again.

 

[arnelgp]

You can download the db here (BOM).
Not sure how to make this relationship - 3 level BOM - Microsoft Community

 

[KitaYama]

@arnelgp
Million thanks. I'm in the middle of a very important process. As soon as I'm finished I'll download and see how it's done.

I really appreciate your help.

 

[KitaYama]

I think of this the other way visually so I would do your joins slightly differently

sales? orders>orderlines>products>assemblies>products_1

Products would link to assemblies on productPK to kitFK with a left join (since not all products have an assembly)
the _1 is the same products table, just aliased to appear a second time

to determine the full list of products for an order you can use the nz function

nz(product_1.productPK, productPK)

Just modified your database
The relationships now look like this (I've changed the name of KitFK)
View attachment 104866

because of the left join, you need an aliased query, kept separately here

qryAssemblyProducts

SELECT *
FROM tblProducts INNER JOIN tblAssemblies ON tblProducts.ProductPK = tblAssemblies.ProductFK;

To list all products required for an order you then have this query
qryRequired

SELECT tblOrders.OrderPK, tblOrders.CustomerPK, tblProducts.ProductPK, tblProducts.Product, tblOrderDetails.OrderDetail_ProductCount, qryAssemblyProducts.UsedCount,
">>>" AS spacer, Nz([qryAssemblyProducts].[ProductPK],[tblProducts].[productPK]) AS ReqProductPK, Nz([qryAssemblyProducts].[Product],[tblProducts].[product]) AS ReqProduct, Nz([qryAssemblyProducts].[usedcount],1)*[OrderDetail_ProductCount] AS ReqQty

FROM (tblProducts INNER JOIN (tblOrders INNER JOIN tblOrderDetails ON tblOrders.OrderPK = tblOrderDetails.OrderFK) ON tblProducts.ProductPK = tblOrderDetails.OrderDetail_ProductFK) LEFT JOIN qryAssemblyProducts ON tblProducts.ProductPK = qryAssemblyProducts.ParentProductFK;

The spacer column is just to separate data from the calculations

I added an assembly item to your order 2 to demonstrate and attached your modified db

@CJ_London Unfortunately I'm still in a Zoom meeting and even though I downloaded your demo, I've not been able to test it.
But seeing the relationships and reading your description, I'm sure it's perfect for what we have in mind. I think I couldn't come to this result in a hundred years if I was on my own.

still 2 simple questions and I promise I won't bother you more.
1- It's the same question as in #21. I just want to be sure it won't cause troubles. When we have an order of a single (not assembled) product , there's a record for it in tblOrders and a record in tblDetailsOrder. Both records have the same ProductFK. Is it OK?

2- When we receive an order for a product that contains 50 or 60 parts (an assembly or a kit), after adding a record in tblOrder, how exactly the members should be added to tblDetailsOrders. Should I run a procedure to create a recordset of its child products and run an INSERT query to add them one by one?
Or is there some other more convenient way?

Thanks again for your time and help.

 

[CJ_London]

1. tblOrders should not hold the productFK, just header information - ordernum, customerFK, orderdate, etc , product FK is stored in the orderdetails table together with OrderFK and quantity- see my example. You need to remove the orderproductfk field from tblOrders.

2. not sure I understand, - you add to orderdetails, not order for the order is for an assembly, they just enter the partnumber for that assembly. The app knows from the assemblies table what parts go into making the assembly - again as demonstrated in the db I sent

 

[KitaYama]

1. tblOrders should not hold the productFK, just header information - ordernum, customerFK, orderdate, etc , product FK is stored in the orderdetails table together with OrderFK and quantity- see my example. You need to remove the orderproductfk field from tblOrders.

2. not sure I understand, - you add to orderdetails, not order for the order is for an assembly, they just enter the partnumber for that assembly. The app knows from the assemblies table what parts go into making the assembly - again as demonstrated in the db I sent

Got it.

I think all my questions are answered and I have the solution I was looking for.
Many many thanks.

 

[gemma-the-husky]

I agree Pat.

I don't know if you remember, but @KitaYama has a really complicated and sophisticated system of managing product traceability - I am sure you contributed to that discussion - and I was surprised that it didn't already manage inventory in order to inform the traceability.

 

[KitaYama]

and I was surprised that it didn't already manage inventory in order to inform the traceability.

@gemma-the-husky
As I explained in PM, that was for line 2 & 3 (machining) and the system you're talking about is under the last stage of testing. It will be on-line from 2023/01/01

This question was for Line 4 (Sheetmetal). And as I explained at present this production line doesn't need an inventory.

I'm confused about why you would be breaking down the assemblies in an order entry system.

@Pat Hartman I'm sure how many times I try to explain, it would be hard to understand it. let me give you an example.
An average assembly has 50 to 400 components. I take the following assembly as an example. It has 352 parts. Get ride of the ready made components (screws, hinge, etc). 311 components are left. All of these 311 components are our products.

We have received an order for this assembly. (3200 Pcs)
From the point of ordering, it's only one order with the quantity of 3200. I understand it. And as you expect there should be only one record for this order in order table. You are correct up to this point.

But from the point of production, we have to manufacture 312 component. For us, it's not one order. We have to manufacture 312 products. And We need to be able to trace the production processes, who has done what, which machine and operator? is it finished? if not at what stage of production it is? when will it available for being assembled? etc.

Sometimes the customer calls us and asks if it is possible to do some changes to one of those 312 components. We can not leave our desks and spread in the whole company to find out the progress of that specific component. OR where it is? who is in charge of its production? etc. Every piece of data about that assembly and it's components should be available for us at any moment.

To be able to have this information, we have an Order table and a DetailedOder table. Even if it's one order, we save all the components in the detailed table. Then there's a processes table that has a FK to to detail table's PK. (one to many relation). The processes of each those 312 components are saved in the process table.

This is the search result of one of those component.

This is the situation of one of those 312 components.
The left side is the single form of tblOrdersDetail. The right side is a continuous sub form linked to process table.
The blue rectangle is the assembly we have to produce. (It's shown here only with dLookup from tblOrders).

At the left side, you can see the process and who and when it's been done. The empty textboxes shows that process has not been processed yet.
the filled one is the name of the operator and the date. A double click on any of them, brings up a more detailed form about each process, machine, (for security reasons I can not show it here)
There's also the name of who is in charge of this part's production. We can send a message to him through this form and ask him to stop/continue the production or ask for any kind of change.


Again to be able to have this data, even if the order is one order, for us it means manufacturing 312 components. And this is only one order. There are more orders. And if we don't manage it this way, who can take care of the mess?

If we don't have a reliable system to check these info, we have to go to shop and search for each one of those component and I don't need to explain how hard it would be.

You may not believe it. But this form feeds the production machines too. This database creates and sends all the NC data for CNC machines. The operator searches for the component he want to work on and click a button. All the necessary NC data is sent to his machine and he only presses the start button.

I hope I'm clear enough.

You have a better idea? I'm all ears.
Thank you for your interest and trying to help

 

[KitaYama]

I saw you mentioning BOM earlier. The only BOM I'm familiar with is in CAD system. I have it in my list of surveys and studies.

Thanks again

 

[KitaYama]

I'm the one who build them. As I said we have it in our CAD and on all our drawings.
I'm trying to understand (study) how to use them in our database.

 

[MsAccessNL]

I am working on a similiar project. I am building a serialized bom structure for a Dutch company building solar cell calibration equipment.. I started with searching for examples. Here are some links of software sites with info about Bom inventory and MRP (Material Resource Planning). I am following your posts. These are the links i found interesting:

https://support.axolt.com/docs/erp-documentation/manufacturing-service/work-order/

https://support.accountingseed.com/...4-Kit-Products-with-Sales-Orders-and-Billings

https://www.inflowinventory.com/sup...kits-out-of-raw-materials-using-a-work-order/

https://learn.erpag.com/project/manufacturing/work-order/bom-based-work-order

https://realtrac.com/our-solution/purchasing-and-inventory/bill-of-material/

Bom en Mrp (Material resource planning) software

 

[RogerCooper]

I am the VP of MIS for a manufacturing firm. I would strongly suggest you get an ERP designed for a manufacturing company rather than try to recreate it piecemeal in Access. (We use Visual Manufacturing from Infor). You need do a lot more than track the parts that are used to make a product. There are purchase orders, work orders and customer orders. You need to track inventory and labor operations. In Access is a very useful tool, but you can't expect it to do everything.

 

[MsAccessNL]

I am the VP of MIS for a manufacturing firm. I would strongly suggest you get an ERP designed for a manufacturing company rather than try to recreate it piecemeal in Access. (We use Visual Manufacturing from Infor). You need do a lot more than track the parts that are used to make a product. There are purchase orders, work orders and customer orders. You need to track inventory and labor operations. In Access is a very useful tool, but you can't expect it to do everything.

I agree with you in terms of investement and time. I already proposed to my client to look at already available systems. I think, in my case, that they are looking for a tailor made solution (they are producing 6 specialized products). I think it can be done in Access, but it will be a big project and in KitaYama case, he has already made an application and is looking how to extend it with more functionality (if a understand the post correctly).

 

[KitaYama]

but you can't expect it to do everything.

Though I don't know what VP of Mis means, but thanks for the advice, but we're doing great. An out of box ERP, is an out of box and is designed in a way to cover an average requirement for an average company. It's not meant to be customized to a particular need and we have to use it As-Is.

But with an in-house application, the case is different. We need to implement a new rule? The next week, the new rule is in place and running.
You may be surprised by what Access is doing for us? Which ERP can read data from PDM and send it to a target CNC machine?
Which ERP can be customized per individual users' need?

 

[gemma-the-husky]

VP is vice president. Not sure about mis.

That's the benefit of choosing bespoke development. You get what you want. You don't have to make compromises. Improvements are quicker. It's free (apart from the internal costs, which might not be a saving, as the development is not shared). It might give you a competitive advantage.

I wouldn't be surprised if your system is better than your competitors. It can happen when you really understand both development and your own requirements.