Relationships

How do you determine which field to link with which field? I have linked PK with PK but it won't let me enforce Referential Integrity as no unique index found for the referenced field of the primary table (but my PK IS uniquely numbered so I am confused!)

RE: Relationships

Hi Stephen
Thank you for your speedy response. I think a week on the course rather than a day may have been in order lol! I work for a charity so have a members table. I have the whole membership database on an excel spreadsheet and have split it up into about 10 or 15 tables with no more than about 13 fields per table. I have tables for events and committees, subs, etc. I am trying to do this step by step. Am I right in thinking that most of my tables have a many to many relationship? If that's the case do I need to make an intermediary table for each and every Committee/Event table? HELP - this is too confusing!
Tania

RE: Relationships

Hi Tania,

I should first make it clear that I am a delegate like you not a tutor. Incase you are still looking for help I am willing to try and help. You have mentioned three tables that you have.

All of your tables should have a primary key field, which should be Long Integer type. If table 'A' is linking to an intermediary table or to any child table, then the primary key in table 'A' will link to a field that is NOT the primary key in the other table (lets call it 'B'). The link field in table B will be a long integer field placed in the table specifically for the purpose of linking to table A.

For the members and subs tables these would probably be best linked as many to many with and intermediary table. Although subs as a many table without intermediary is also possible.

With regards to committes this depends whether you are talking about committee meetings, about which members belong to which committees or both. To simply keep records on who belongs to which committees then this will be one to many with members being on the many side, and the committees being a look-up table.

I hope this helps.

Regards, Mark

RE: Relationships

Hi Mark
thanks so much for offering to help! I have spent a couple of times trying to sort out my many "many to many" tables and got myself in a tizz because I have approx 2500 members already. I think I need someone actually with me on this looking at what I'm actually working with. Thanks again. Tania