I have a database that contains information for members of a certain organization. Something Like this:

<b>TBL_MEMBERS</b>

MemberID
MemberFirstName
MemberLastName
MemberTitle
CategoryID

The CategoryID field is taken from another table like this:

<b>TBL_CATEGORIES</b>

CategoryID
CategoryName

The first 3 fields in TBL_MEMBERS are pretty straight forward... The members first name, last name, and their job title. The last field in the table (CategoryID) is where I am running into problems. The CategoryID is taken from TBL_CATEGORIES. I need each member to be able to be classified under one OR MORE categories. For example, one member may be in the "advertsing" category but another member may be in both the "advertising" and "Marketing" categories.

My question is, how can I specify that a member is in more than one category in the TBL_MEMBERS? Do I need to setup the database differently?

Thanks in advance. Any Help is very much appreciated.



Edited by - flexman44 on 30 Apr 2003 03:19:30

Anybody...???... I really need help with this... Any Help is appreciated.

Thanks

Edited by - flexman44 on 30 Apr 2003 03:20:51

Yes, create an intermediate table so you can set up a Many-to Many relationship:

<b>TBL_MEMBERS</b>

MemberID
MemberFirstName
MemberLastName
MemberTitle


<b>TBL_CATEGORY-MEMBERS</b>

MemberID
CategoryID


<b>TBL_CATEGORIES</b>

CategoryID
CategoryName


Take a look at this: www.tdsf.co.uk/tdsfdemo/Tutorial04.htm for an example of a Many-to Many relationship.

Regards

Owen.

-------------------------------------------------------------------------------------------
Used programming books and web development software for sale (UK only): www.tdsf.co.uk/tdsfdemo/Shop.htm

Developer services and tutorials: www.drdev.net

Multiple Parameter UD4 / Access 2000 Search Tutorial: www.tdsf.co.uk/tdsfdemo/

Now, using ASP, VB Script (developing with Dreamweaver) how could i do a search for members, without have a member show up multiple times (if the member was listed in more than one category).

For example, if i have a search form, and the user just does a blank search that brings up every member, and a member was listed in 3 categories, the member will be displayed 3 times... I only want the member to be displayed once.

Edited by - flexman44 on 09 Jan 2006 22:34:27