Record with multiple sub categories 1

[Cleis]

Okay:

Lets say that I have a contact record stored in a table called "tblContact" and the field names are as follows:

Last Name
First Name
Title
Categories

This record can have multiple categories assigned to it such as:

Cat1
Cat2
Cat3
Cat4

My question is, what is the best way to store this category data? Should I store the various categories in my base table "tblContact" in a field seperated by commas? Outlook does this but uses "//" instead. . . . I believe.

Or should I . . .

Save these categories in a seperate table which is linked to the main table via an index; I then could create a "Group By" query on "tblContact"and then run a crossab over the secondary table containing the category information and join both queries together on the index field.

The end result that i'm looking for is the following which will feed the recordsource of a form and report.

LastName,FirstName,Title,Cat1,Cat2,Cat3,Cat4[/color blue]


Does any of this make sense?? can someone at least point me in the right direction? Me thinks that option 2 is a better approch than the 1st as I'm not sure how I would parse 1//2//3//4// or 1,2,3,4 into Cat1 Cat2 Cat3 Cat4


Many thanks!


Itch

 

[Remou]

The normal way to do this is with three tables.

[tt]Contact
ContactID - PK

Category
CategoryID - PK

CategoryContact
ContactID
CategoryID[/tt]

You may wish to read

http://www.r937.com/relational.html

 

[Cleis]

Thanks! Sometime I get too close to the trees to see the forest.

Here is a star!!