Linking Tables

[Larry]

Hi all,

Does anyone know how to better link two tables? I have one
table called tbl_INDIVIDUAL, it has the following field:
InterviewID, Courses, Systems, Areas, Hobbies, SchoolName,
LastName, FirstName, Email, & Tel. On the other table
called tbl_SUMMARY, it has InterviewID, ID (autonumber),
SchoolName, Courses, Systems, Areas, & Hobbies. The
tbl_SUMMARY summarizes the tbl_INDIVIDUAL by SchoolName. I
have 15 SchoolName and so tbl_SUMMARY has 15 entry in
Courses, Systems, Areas, and Hobbies. What I would like to
have happen is that when a user goes into tbl_Individual to
add a new InterviewID, it automatically just append the new
record in tbl_SUMMARY. Is this possible?

Thanks for any help on this.

Larry

 

[Jim Carlock]

Larry,

I'm thinking along these lines:

1) a table for school information, tSchools
2) a table which will link to tSchools, named tCourses and you can
set up an AutoNumber to in tSchools and call the field id,
then set up a field called p2tSchools which is linked to the
tSchools.

Number 2 above assumes you will have many courses that
are in each school. It also works in the following manner
regarding the course names. The course name works in
conjunction with the field p2tSchools as the primary key.
This means that you can have the same course inserted for
two different schools.

One step better would be to create a connecting table so
you get a many to many relationship. This third table will
be named cnctCourseSchool and will hold two fields,
SchoolID and CourseID. Both will be Long number types,
and will connect the two tables, but it also means that you
would have an id field inside of tCourses that would be the
primary key, an autonumber type.

I would also instead of use tblIndividual as a name, give it a
little more descriptive name, like tCustomers, or tClients or
tProspects. If you go the tProspects route, you'll have a
separate table for customers, and in this manner the two
tables are kept separate and will require a little extra effort
in moving a prospect into the client category table.

And one other thing, about the Summary table, I personally
would not create a summary table, unless it's a table that will
be programmatically created and deleted. Summary is a name
that tends to indicate that a report is being drawn up and you
would usually use a query to create such things. Sometimes I
will use a make table query to create a new table to hold
summary information. In that case, I'll prefix the table name
with tmp so as it'll be named tmpSummary and if anyone goes
to look at it, they'll know it's a temporary table.

Hope this information helps. Let us know.

--
Jim Carlock
http://www.microcosmotalk.com/
Post replies to the newsgroup.


:
Hi all,

Does anyone know how to better link two tables? I have one
table called tbl_INDIVIDUAL, it has the following field:
InterviewID, Courses, Systems, Areas, Hobbies, SchoolName,
LastName, FirstName, Email, & Tel. On the other table
called tbl_SUMMARY, it has InterviewID, ID (autonumber),
SchoolName, Courses, Systems, Areas, & Hobbies. The
tbl_SUMMARY summarizes the tbl_INDIVIDUAL by SchoolName. I
have 15 SchoolName and so tbl_SUMMARY has 15 entry in
Courses, Systems, Areas, and Hobbies. What I would like to
have happen is that when a user goes into tbl_Individual to
add a new InterviewID, it automatically just append the new
record in tbl_SUMMARY. Is this possible?

Thanks for any help on this.

Larry