Creating a search for a many to many relationship

[npark]

I'm rather new to Access. I have created a database that has a many to many database. The first table is the vendors name,address,ect. The second database is all the products that my company will buy. I have a third table linking them each vendor has the products attached to what they sell, and the products all have the vendors attached that sell it.

I need a way to search either a vendor and see what they carry or search the products and see which vendors sell that product. I have no idea how to set this up. I will eventually have to put this on the network for others to use so I hope to make it somewhat easy to use.

Thank you in advance for your help.

 

[JoanneM]

Hi there

when dealing with many to many relationships you have to get your head around a junction table

in you case to create this you would have a field VENDORID and a field PRODID.

To compile the data to populate this table is in two stages

stage 1 Create your data

1: create a table with only VendorIDS

2: create a query add this new table to the query
add the products table to the query. Join on VENDOR

3: into the query grid put VENDORID field and the
PRODUCTID field

4: Make a table from this query.

stage 2 Clean up your data

5: using the query wizard create a find duplicates using
the table you have just created.

6: Delete the duplicates

You now have your JUNCTION TABLE ready for use

Hope this helps

JO

ps
please remember to populate this table when any new product or vendor is added to whichever database or all your hard work will come unstuck

 

[Rolliee]

The writers of WROX Publishing - the best in my opinion say you should not use a many to many. They say that a many to many can be made to a many to one and a one to many which is easier handled.


rollie@bwsys.net