After a long while doing research, i've discovered a SRM2M Relationship is what I need here, but i fail to understand it quite well: this is the problem.

My only table format is "IDproduct, product, cost". We have tons of products, but only some of them are normally for sale: because some products are their components, but they are living on the same table. I we selled cars, in this table i would have, for instance, the registries:

1 CAR
2 ENGINE
3 10mm SCREW

So, what i would need is a self referenced many to many relationship to indicate which parts are components of other parts and viceversa. Of course, many parts can have the same components, and many components can form the same part.

¿Is this the way to go? Many have told me "Split it into two tables: Components and FinalProducts!". But the problem is that i have parts (like ENGINE) with aren't final products but still have components....

¿Maybe three tables? ¿BasicComponents, MidComponents and Final Products?


¿To complicate this even further, does MS Access support this type of relationship? Because It's a simple program and i would like to keep it in Access.

how about:

product_id, product
1, car
2, engine
3, screw

product, component
1, 2 (engine is a component of car)
1, 3 (screw is a component of car)
2, 3 (screw is a component of engine)