Many to many relationship

[paulcjcross@btinternet.com]

How to set up a many to many relationship. I know you need a third
table to join the other two. I need one table (table1) with
stock_numbers(u nique), one table (table2) with job-numbers(unique) and
I want to join them with a table (table3) with job-number linked with
a stock-number and the number-of-units-of-that-stock.

how is it done? so that the relationship window shows up:
table1 many to one table3
and
table2 many to one table3

Paul

[Phil Stanton]

Re: Many to many relationship

Assiming Table 1 has a Key StockNumberID (Auto, PK)
Table 2 has a Key JobNumberID (Auto, PK) - I hate
underscores

Your Third table should be

StockNumberID
JobNumberID
UnitsNeeded

You must set both StockNumberID and JobNumberID as joint Primary Keys
(Highlight them both & press the Key symbol)

Then set up your relationships with the 3 tables linking the obvious fiels
together

This will allow you so see all the stock reqired for each job or which jobs
are using a particular stock item

Phil

<paulcjcross@bt internet.comwro te in message
news:a86ce9d0-e0ad-467e-9d49-7666253f7653@k3 0g2000hse.googl egroups.com...

How to set up a many to many relationship. I know you need a third
table to join the other two. I need one table (table1) with
stock_numbers(u nique), one table (table2) with job-numbers(unique) and
I want to join them with a table (table3) with job-number linked with
a stock-number and the number-of-units-of-that-stock.
>
how is it done? so that the relationship window shows up:
table1 many to one table3
and
table2 many to one table3
>
Paul

[Larry Linson]

Re: Many to many relationship

The relationship window will reflect the actuality that you are using a
junction/intersection table to implement many-to-many. If you want
something different, you need to find a third-party relationship display
that does what you want -- I am not aware of any that do.

I have never had any problem with the relationship window reflecting what
actually _is_ rather than some abstracted view of "what it ought to be".

Larry Linson
Micrososoft Office Access MVP

<paulcjcross@bt internet.comwro te in message
news:a86ce9d0-e0ad-467e-9d49-7666253f7653@k3 0g2000hse.googl egroups.com...

How to set up a many to many relationship. I know you need a third
table to join the other two. I need one table (table1) with
stock_numbers(u nique), one table (table2) with job-numbers(unique) and
I want to join them with a table (table3) with job-number linked with
a stock-number and the number-of-units-of-that-stock.
>
how is it done? so that the relationship window shows up:
table1 many to one table3
and
table2 many to one table3
>
Paul