relationships??

[hi there]

hey folks,

i'm have some questions about relationships in tables and setting up joins in queries:

A. when do you need compound primary keys. if i'm using autonumber fields for my primary keys for all of my tables and then have a table that brings in information from more than one table i was told to use a compound primary key. could someone explain whether this is correct and if so what's the reasoning behind this.

B. do joins created using SQL only reside in memory and cannot be used to enforce referential integrity? if so how come when i don't explicitly create joins in the relationship window they appear connected in the QBE pane?

C. finally, what's the difference when setting up relationships in creating a foreign key in a table and using the lookup wizard to create a field in the table which includes all of the fields from the foreign key table.


many thanks for any responses. have a good day.

 

[hi there]

thanks for the response, pat it is quite informative. i'm still a little unclear about many-to-many relationships. using your example i was thinking that to relate students to classes it would be a one-to-many relationship. so that 1 student can be enrolled in several classes. i guess i'm still confused. an example of an application that spawned this question is composed of the following tables:

1. esourcestbl: fields - sourceID, sourceName, sourceDescription
2. reqstbl: fields - reqID, reqType, reqParameter, .....
3. recordtbl: fields - sourceID, reqID, separateReport, reportParty.....

in the recordtbl is has the reqID and sourceID (foreign keys) as a compound primary key for the table. i was wondering what's going on here. is this a many-to-many relationship? if so could you explain a little more about many-to-many relationships. as far as my part C question in the original post, i guess i was talking about relating table 1 to table 2 by using the lookup wizard in table 2 on a field in table 2 and including all of the fields from table 1 in the SQL statement for the row source.

thanks again for you response

 

[crstphr2]

I can help with the many to many.

3 tables

1) Students
2) Classes
3) table3 contains the studentID and the classID creating 2 one-to-many relationships.

One student can have many classes, likewise one class can contain many students.

Good luck.

 

[hi there]

thanks for the responses crstphr2 and Pat. i think slowly this many-to-many thing is starting to sink in. also thanks for the responses about the lookup fields Pat, i think i'm starting to realize that tables should strictly be used for data storage and referential integrity. if i'm understanding your post correctly, it looks like well written queries should be the backbone of applications that bring everything together. i think based on what your saying i should also be basing all of my forms on queries versus using tables as the recordsource.