PProduct composition

[gtsolid]

Hi,


I'm looking for a query which ask the name of the group eg. "110-05" then read how the group is made (a table is made for each group composition) and tells me the warehouse situation. The are 2 warehouse tables raw and finished items used to assembly the final product.
For example I need 1 pc of 110.00.104 to mount a 110.05. So I go into the other table and i see I have 13 pcs of 110.00.104 that are ready (table "Magazzino") and 90 pcs that are 50% in the production line (raw material)


I attach a smaller version of the db.
At the end I'd like to get a table in which I have [Codice;Qnt_required;Qnt_available;Qnt_raw]

[mike60smart]

Hi
Sorry to say this but you will need to start again with the construction of your database.

All of your tables have a PK - Short Text

All tables should have a PK - Autonumber which are used to uniquely identify each record in each table.

You have not set any Relationships between tables as shown in the attached Relationship Screenshot

[June7]

A table for each group composition is not optimal db design. Really should be 1 table with another field for group ID linked to a parent table with each group as a unique record.

[gtsolid]

A table for each group composition is not optimal db design. Really should be 1 table with another field for group ID linked to a parent table with each group as a unique record.

Can you explain better?
I can can create a table with parts and another with groups. I can link them with a third table in which a put 1 where the raw of part crosses the column of the group it can be mounted:

and then how to write a query tell me the number of "part_x" missing to mount a complete "Device_x"?