Unique Values in Two Tables

[Waqas ali]

Dear All,

i have two tables with unique items. for example in table1 i have unique suppliers and in table2 i have unique items but some items i am receiving from more than one suppliers.

how can i make combination of suppliers and items.

if i select supplier 1 it should show which items he is supplying me.

if i select supplier 2 it should show which items he is providing me.

supplier 3 is supplying 10 items from 10 one supplier 1 is and one supplier 2 is supplying.

how can i make combination of supplier and items?

 

[Aladin Akyurek]

Try to post a scaled-down sample along with the desired results.

 

[Micron]

Easy but improper approach would be to allow the part info to be repeated in the table for each additional supplier. The proper way would be to create a linking table. The relationships between the 3 would be something like below. Hopefully you can see that ST15678 is supplied by companies ABC and Acme.

SUPPLIER
ID	Name	Address	City	Etc.
1	ABC Corp
2	XYZ Ltd
3	Acme

<tbody>
</tbody>

MasterPartsCatalog
ID	StockCode	SuplrPartNo	Desc	Etc
1	ST15678	123456
2	ST99874	789456
3	ST55887	784573

<tbody>
</tbody>

MPC_Suplr
MPC_ID	SUPLR_ID
1	2
1	3
2	1
3	1
3	2

<tbody>
</tbody>