linking tables

[Guest]

how can i link two tables - one being a contact list and the other a series
of ongoing assessment data. in essence i want to link the assessment data to
EACH individual. surely i dont have to creat a table for each individual...
or do i???

help me....

tomoc17

 

[Guest]

It sounds like you are describing a many-to-many (M:N) relationship. A M:N
relationship is created by using a third table, known as a join table (also
known as "linking" or "intersection" table). The join table has the many side
(foreign key side) of two one-to-many (1:M) relationships.

You should spend some time gaining an understanding of database design and
normalization before attempting to build something in Access (or any RDBMS
software for that matter). Here are some links to get you started. Don't
underestimate the importance of gaining a good understanding of database
design. Brew a good pot of tea or coffee and enjoy reading!

http://www.datatexcg.com/Downloads/DatabaseDesignTips1997.pdf

http://www.datadynamicsnw.com/accesssig/downloads.htm
(See the last download titled "Understanding Normalization")

http://support.microsoft.com/?id=234208

http://support.microsoft.com/?id=289533

Also recommended: Find the copy of Northwind.mdb that is probably already
installed on your hard drive. Study the relationships between the various
tables (Tools > Relationships...)


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[John Vinson]

how can i link two tables - one being a contact list and the other a series
of ongoing assessment data. in essence i want to link the assessment data to
EACH individual. surely i dont have to creat a table for each individual...
or do i???

help me....

tomoc17

Do follow Tom's recommendations, but... if each Contact has many
Assessments, and each Assessment applies to one and only one Contact,
you have a very typical one to many relationship. All you need is to
have a Primary Key field in the Contact list (ContactID maybe), and a
field of the same size and datatype (use a Long Integer if the
ContactID is an Autonumber) in the assessment table. A Query joining
the two tables on these fields will give you the link you want; or you
can use a Form based on the Contacts table with a Subform based on the
Assessment table to display the assessments for each contact.

John W. Vinson[MVP]