One record multiple categories

[anita27]

Hi

I've built a contacts database which consists of one table containing contacts and services provided and a table with a list of categories, each has a PK autonumber id field. My problem is that individual contacts could fall into multiple categories, could somebody tell me how to do this.

Many Thanks
Anita

 

[MareTino]

I'ld use an extra table (eg "CONT_CAT_COMBO"). Now each contact can have multiple categories and each categorie can be assigned to multiple contacts.

CONTACTS CONT_CAT_COMBO CATEGORIES
ContactID CCC_ID CategorieID
Name ContactID CategorieName
.... CategorieID .....
... .....
..
.

 

[anita27]

Hi

Thanks for this but I'm afraid I am still confused.

Anita

 

[The_Doc_Man]

Think of it like this:

You've a list of contracts that you track. You have the data from them. But they have something else that potentially differs from one contract to the next, and you call it a "category."

Well, if the contract category depends on the individual contract, then you can store a category code or name in the same record where you store the rest of the contract data.

First, decide exactly what you want to store. Like, a code, an abbreviation, a long name, ... anything you need.

So go into design view on your contract table. Add a new field called ContrCat or Category or CC or whatever floats your boat.

Now save the table and open it in datasheet view. You will see a column of blanks. To do this right, you must go back and supply the categories for the contracts having blanks.

On the form you are using to store contract data, go into design mode and add a text box bound to the new category field. Now you can maintain the category through your form.

On any reports that need to know this, one at a time, go into design mode and find a place to put a text box for category. Now you can report your contracts by category.

If you want to take the simplest approach, that's all there is.

 

[neileg]

You have a many to many relationship. Look this up in the Sample Databases section here.

 

[anita27]

Thank you I've now managed to suss this.

Thanks for all your help

Anita