MS AccessLink Files

I have created 2 basic files
A Vendor file and an Activity file.
I want to create a link file with a many to many relationship.
That is a Vendor can be linked to many activities and an Activity can be linked to many vendors.
I. E.
VendorA <--> ActivityA
VendorA <--> ActivityB
VendorB <--> ActivityA
VendorB <--> ActivityB

I create my link file with a Vendor Field and a Activity Field but it allows me to create multiple entries as I really want the unique key on the concatenated values of Vendor Field and Activity Field which it doesn't allow me to do. Therefore I end up with duplicate records in my link file.

I know this is a very basic problem but I see no simple solution for us dunderheads.

Thanks Ted

 

I'm not sure what you been by access link files..

 

When I say "file" I mean table.
A Vendor table
An Activity table
The "link file" is a table which defines a relationship between Vendor and Activity.
Sorry for the confusion - mainframe background.
Ted

 

this is a pseudo many to many, meaning that you can have unique keys in both tables. Use an intermediate table to hold the man to many relationship.

Table 1
VendorID (PK)
Vendor_Name
etc etc...

Table 2
ActivityID (PK)
Activity_Description
etc etc..

Table 3
VendorID
ActivityID

 

Your pseudo code is exactly what I need to do and is exactly what I 've tried to do.

My problem is in table 3 I can create duplicate records as I can't create a key which combines the keys from the first two files and make it unique.

Do you see a solution for this?
Ted

 

you can't have a unique key like that becuse it's a many to many so what you have to do is make your relationship in your query.

You will use a join.

something like this
SELECT Table2.Desc
FROM Table2 INNER JOIN Table3 ON Table2.ActivityID = Table3.ActivityID
WHERE (((Table3.VendorID)=[@vendorid]));

 

Kodo
Thanks for your help.
Life is much better now.
Ted

 

glad it worked out for you