kategories

[Wolfgang Schinwald]

Can someone help me with table design. In my old Q&A Database (a Symantex
Product) I used to put my address entries into categories: friends, work,
school, computer, etc. Everybody could be a member of one or more
categories. So I was able to produce reports in which I could select the
Kategory (Criteria). In the old (flat) database this was possible in one
table. The category entries were devided by semi colon. Now I want to
transfer my data to an Access Database.
Can anybody show me a sample database that I can study. The problem seems to
be the same with hobbies (more than one possibilities) or with people who
are members of one or more departments in a firm.
Any help appreciated

 

[tina]

you're describing a many-to-many relationship. each category can have many
contacts, and each contact can have many categories. in Access, you can't
express a many-to-many relationship directly. instead, build two tables, as

tblContacts
ContactID
FirstName
LastName
etc, etc, etc.

tblCategories
CategoryID
CategoryName
etc, etc.

then build a third table, called a linking table, to record each connection
of contacts to categories, as

tblContactCategories
ContactID
CategoryID

example:

tblContacts
ContactID FirstName LastName
1 John Smith
2 Jane Doe

tblCategories
CategoryID CategoryName
1 Friend
2 Work
3 School

tblContactCategories
ContactID CategoryID
1 1
1 3
2 1
2 2

hth

 

[Wolfgang Schinwald]

Thanks a lot, I'll try it out