A normalisation question (3 Viewers)

[EH_SG]

I created a database for my employer 2 years ago, initially to track component stock though it has broadened to encompass more.
It seemed best at the time to have a separate table for (1)the components (parts) that make up (2)sub-assemblies (sections) that make up (3)products. I now wish I had created 1 table that has all of the above in with a field(s) that define a category of parts/sections/products and 1 table that defines which items are required for other items. (I have attached a screen grab of my relationships to try and give a picture please be kind!)
I have a host of forms and reports that deal with each of the tables separately and sometimes brings a similar query for each table into 1 report eg overall stock value report.
I have wondered a few times if I should just suck it up and combine the 3 tables into 1, adjusting all the forms and reports as necessary but it has always felt like it would be a waste of time however I now wish to create a delivery note function and I am feeling the same pressure again as we do not only send out products but also sub-assemblies and components.
Does anyone have any wisdom for me? Should I bite the bullet and change it now or should I be using some form of union query to give the list of possible items to go onto the delivery note?

 

[mike60smart]

Difficult to make out your relationships from the screenshot.
Are you able to upload a copy of the database with no confidential data?

 

[mike60smart]

This is a back up of the tables (only) I created before I moved them to sharepoint so its not exactly like this now but the structure is the same. And this doesn't have all our customer contacts etc in.

Hi
Which are the table that you are thinking of modifying?

 

[EH_SG]

This is a back up of the tables (only) I created before I moved them to sharepoint so its not exactly like this now but the structure is the same. And this doesn't have all our customer contacts etc in.
(Repost without some numbers in!)

 

[EH_SG]

Hi
Which are the table that you are thinking of modifying?

Inventory, Sections and Products - each then has its own transactions table, which should maybe be 1 table also if I merge the initial three

 

[mike60smart]

Can you explain each of the 3 Transactions please.

 

[EH_SG]

These tables log each stock transaction for inventory, sections and products respectively

 

[mike60smart]

These tables log each stock transaction for inventory, sections and products respectively

Sorry but your tables and relationships do not make sense to me.
Points I have picked up are as follows:-
Numerous Lookup field in numerous tables.
You should not have any spaces in fieldnames or any non numerical characters ie !"£$%^&*(){}[]?><~@
Some of your Joins between tables do not have Referential Integrity enforced.

Some of the Guru's may give you further guidance.

 

[mike60smart]

I tidied up the main tables as shown in the attached screenshot

 

[mike60smart]

It would help if we could see the FE?

 

[The_Doc_Man]

In response to a verbal part of your question - ignoring the diagrams because mike50smart is looking at those - it has been discussed in several threads that you do better to call a sub-assembly a part. Then - presuming that all the parts of the sub-assemble are also individual parts in the main table - you can create a self-reference table (a.k.a. JUNCTION table) where you have an assembly part number (that refers to the assembly as a whole), a component part number (that refers to a part used to make the assembly), and the quantity of that component you need. There COULD be more in the junction table, but what I listed would count as a minimum. For multi-layered cases, you can have a component that is ALSO a sub-assembly. Things can get a little bizarre, but technically the junction table is the way to go.

In this forum, look up "assembly" or "component inventory" to see more.

 

[Ken Sheridan]

It seemed best at the time to have a separate table for (1)the components (parts) that make up (2)sub-assemblies (sections) that make up (3)products. I now wish I had created 1 table that has all of the above in with a field(s) that define a category of parts/sections/products and 1 table that defines which items are required for other items.

You can model products, assemblies (which might be sub-assemblies), and atomic base parts very simply with two tables. In one table each product, assembly and base part is a value in the same column in the table. The other table simply references two instances of the first table by referencing the key of the first table in a MajorPartNum or similar foreign key column, and in a MinorPartNum or similar foreign key column. A third column, Quantity, records the number of the minor part, which can be an assembly or base part, in the major part, which is an assembly or product of course. Other columns representing other attributes of the relationship can be included if necessary.

From this simple model a bill of materials for a product or assembly of arbitrary complexity can be generated at runtime. This requires recursive querying however, which unlike client server products like Oracle or SQL Server, Access does not support. It can be simulated however, and the attached zip file includes two apps which illustrate this. BoM.accdb builds a BoM to an arbitrary number of levels by progressively inserting data into a table in an external database created at runtime, whereas the simpler PartsTree.accdb does so to a fixed number of levels with a query which joins multiple instances of the tables.

 

[EH_SG]

I have tried to improve the relationships view to make it clearer how everything works together currently.
I do have junction tables (part to section and section to product)
@Ken Sheridan it sounds as though you are saying that if I had all the parts, sub-assemblies and products in one table then it is a more tricky process to get a list of all parts in one product? At the moment I achieve this by a query that incorporates both junction tables.

I know my database isn't perfect in lots of ways but its a question of could I/should I put the work in of having 1 table rather than 3 with just 1 junction table defining what makes up what? Will it be worth it not only to make the delivery note task much easier but anything else going forward eg an orders function and will it make a more efficient database?

 

[EH_SG]

Sorry but your tables and relationships do not make sense to me.
Points I have picked up are as follows:-
Numerous Lookup field in numerous tables.
You should not have any spaces in fieldnames or any non numerical characters ie !"£$%^&*(){}[]?><~@
Some of your Joins between tables do not have Referential Integrity enforced.

Some of the Guru's may give you further guidance.

Numerous Lookup field in numerous tables - why is this bad and what is the alternative?

Some of your Joins between tables do not have Referential Integrity enforced - I have no idea what this means in practical terms