Many to many associations

[jonesy]

Hi All,

I have just started a database to keep a record of CAD drawings and specifications. At the moment I just have a couple of tables, "Drawing Detail" and "Specification Detail".

One drawing can have many specifications, and one specification can have many drawings. The way I would associate these is by having tables that look like this (simplified) :

Table : Drawing Detail
Field 1 : Drawing Number (Primary Key)
Field 2 : Specification 1 (Associated specification)
Field 3 : Specification 2 (Another associated spec)
Field 4 : Specification 3 (Yet another associated spec)
etc.

Table : Specification Detail
Field 1 : Specification Number (Primary key)
Field 2 : Drawing 1 (Associated Drawing)
Field 3 : Drawing 2 (another associated drawing)
Field 4 : Drawing 3 (yet another associated drawing)
etc.

This is all well and good, but if I decide now that there will never be any more than 3 drawings associated with a spec, and vice versa......it gets a bit messy if this changes in the future sometime. What i would like to do if have one field in the drawing detail table that can store many specification numbers. Maybe from a list of all specifications, with a check box next to each, so i can just tick the specs that are associated with this particular drawing.
i.e
Table : Drawing Detail
Field 1 : Drawing number
Field 2 : Specifications (All associated specs)

Table : Specification Detail
Field 1 : Specification Numbe
Field 2 : Drawings (All associated drawings)


Is this even possible? Hope someone can help.

Cheers

Jonesy

 

[neileg]

You need a separate table to identify the links.
Eg

TblLinks
Field 1: DrawingNo (links to drawings table)
Field 2: SpecNo (Links to specs table)

Each link would generate a record and this would accomodate as many specs as you want for each drawing and vice versa. This table will have a many to one relationship with the drawings and specs table

 

[jonesy]

Thats what I need.

Hi Neil,

That sounds exactly like what I need, but I don't quite get your meaning. Any chance you could elaborate a little? I'm not brand new to access databases, but never really gone in depth as will have to with this one.

Thanks for the reply Pat, I'm afraid my topic name was a little misleading, I meant one-to-many. I'll search the archives for this instead though.

Thanks again

Jonesy

 

[jonesy]

my head hurts.

Nevermind Neil,

my puny brain worked it out. It's working great thank you.

Jonesy

 

[jonesy]

Hi Pat,

I'd almost got it right but couldn't work out how to prevent duplicates. Thanks very much, your example DB was very helpful.

Jonesy

 

[jontait]

I'm glad you found it useful.

Pat,

I was pointed to your example MANY-TO-MANY by MikeFabro on here...THANK YOU SO MUCH, its just what I needed to sort my disaster of a database out. I can feel my blood pressure slowly returning to normal, god knows when the hair will grow back though!

Thanks muchly

 

[neileg]

A number of people have said the same thing. Maybe I should advertise on national TV - Forget all those hair products that don't work, just buy my Many-to-Many db sample to prevent hair loss

I'll endorse it. I use many-to-many databases and I have a full head of hair at 48!