Creating a bill of materials query from one table

[Authomas]

So i have a table (BOM) which includes all part numbers(including component parts and material), I need a way to make a row in a query/table for each part containing up to 5 different part numbers such as: Part Number| Component part 1| Component part 2| Component part 3 etc. Yesterday i tried for hours to make 5 queries which would do this but couldn't get it to work. Maybe my relationships were wrong but I couldn't think of another way to do this any ideas/examples?



Example of a few rows in the BOM table: Highlighted is the product code or part number and the component part

Below is an example relationship of what I want

[orange]

It would be helpful to readers and communication if you would post a copy of your tables and relationships. Even better would be a copy of your database with only enough data to show the hierarchy and an example of your desired output.
If you are unsure of your design, then I suggest you start with "the 30,000 ft overview of the business", then gradually add some detail such that the key processes are clear.

[xps35]

I think you can't have your BOM in one table. If a part can have more than 1 component you need at least a second table. In your table you see you have to store parts multiple times (see code 02707545). That is no proper design.
If the I formation you want to store about parts and components is not the same, you need three tables.

[Authomas]

I gotcha, how would I design the second table? Is there a way to take the data and process it into a new table? The reason I ask is that we use a software to keep the parts data and then we export it into excel and then into access.

[Authomas]

I added a snip of what I want in the main post. Ill see about making an example database.

[xps35]

I am on a mobile device at the moment so I can't show a model now.

It is possible to process your data to the new structure. The basic idea is to select distinct parts and components from your table into the new table(s). Creating the BOM table should also be possible.
We need more details to give the full picture. Like: can components also consist of components? What info from your table is part, component or BOM data?

[orange]

Bill of Materials is described quite well here to assist with concepts and structure.
I recommend you provide a description of the business/processes in order that readers can understand your tables and relationships.

[Authomas]

The components can consist of components. We make fasteners so in example with the database: The screw is the finished part, a component of a screw might be a screw that has went through a process making it into a new part(we will name this Pre-Screw), the pre-screw consists of metal which has its own part number. So

Row one: Product code: Screw | Component: Pre-Screw
Row two: Product code: Pre-Screw | Component: Metal

Another thing is that some parts may have multiple processes or materials so the Screw in this example could have 5 Pre-Screws as it has to go through multiple processes. Or the Pre-Screw could use multiple metals.

Hopefully this helped a little

[Authomas]

So I believe I got the results I wanted. I believe I just had my relationships wrong. Someone is testing my database right now, but I believe I got what I needed though thank you! Still wouldn't mind getting advice however