Simple Way To Related Multiple Records To Multiple Records

[roystreet]

My situation that I'm very puzzled about is...
I have a project management database which has correspondences as well as projects. I'm trying to Have a way for Correspondence Item be related to Item 2 & 3. I'd also like to have Item also related to Item 4. All of them have a unique Primary Key (PK). I have been experimenting with using a separate table that just holds all of the PK's that ever had a relationship/link added & would use the Foreign Key (FK) as another field. Ultimately, I want my correspondence entry form to have a list box showing all of the items that are related to it & be able to add more or directly click on it to open it up. Does anyone know of a good resource to figure this out?

 

[MajP]

You use terms that no one knows what you are talking about

I'm trying to Have a way for Correspondence Item be related to Item 2 & 3

Huh? You need to talk in generalties

I think you want an easy way to do a many to many relationship, but I do not know what you are asking.

To do a many to many you need a linking table. This usually has a way to store two primary keys as foreign keys.

Example. Assume you are doing a track and field event. And you would have many participants to an event and a single participant could be in many events.

tbl_Athlete_Event
athleteID relates to the athlete table
eventID relates to the event table

then you would have data like

[tt]AthleteID  EventID
   1         a
   2         a
   1         b
   7         b
   4         c
   1         c[/tt]

so athlete 1 is in events a, b, c
and athelete 2 is in a

So what is your question?

 

[Andrzejek]

And you can also go the other way around:

[pre]
AthleteID EventID
1 a
2 a
1 b
7 b
4 c
1 c
[/pre]
In Event a participate Athlete 1 and 2
In Event b participate Athlete 1 and 7
In Event c participate Athlete 1 and 4

Many-to-many relation

Have fun.

---- Andy

There is a great need for a sarcasm font.