Categories?

[Troy D. Young]

I am trying to do something that is probably quite simple, but I simply
cannot think through what needs to happen. I am building a simple contact
database and each record needs to be able to be added to any or all
categories that are available. The categories might be:

Personal
Business
Vendor
X-mas List
Client
Prospect

Of course, I could do this by adding the columns to my table and putting
check boxes on the form, but I want it to be dynamic so that I can easier
enter more categories to a table and have them show up as an option.

Any advice here?

Thanks,
Troy D. Young

 

[John Vinson]

I am trying to do something that is probably quite simple, but I simply
cannot think through what needs to happen. I am building a simple contact
database and each record needs to be able to be added to any or all
categories that are available. The categories might be:

Personal
Business
Vendor
X-mas List
Client
Prospect

Of course, I could do this by adding the columns to my table and putting
check boxes on the form, but I want it to be dynamic so that I can easier
enter more categories to a table and have them show up as an option.

This is a perfectly normal many (contacts) to many (categories)
relationship. You need three tables: Contacts of course; Categories
(with six rows in this example); and Categorization, with fields for
the ContactID and the category. You could display this table on a
Subform with a combo box based on the Categories table.

 

[Troy D. Young]

Hmm...still not quite sure how to do this. Here is what I have:

tblContacts
Fields: AcctID, Name, Address, City, State, ZIP, etc.

tblCategories
Fields: CatID and Category

tblCategoryID
Fields: AcctID and CatID

I have relationships between:

tblContacts.AcctID -------- tblCategoryID.AcctID
and
tblCategories.CatID -------- tblCategoryID.CatID

I think I'm on the right track, but can't quite figure it out...

 

[John Vinson]

Hmm...still not quite sure how to do this. Here is what I have:

tblContacts
Fields: AcctID, Name, Address, City, State, ZIP, etc.

tblCategories
Fields: CatID and Category

tblCategoryID
Fields: AcctID and CatID

I have relationships between:

tblContacts.AcctID -------- tblCategoryID.AcctID
and
tblCategories.CatID -------- tblCategoryID.CatID

I think I'm on the right track, but can't quite figure it out...

This is precisely the correct table structure. To get data into the
tables, and display the categories for an account, you need to get
beyond the very limited table datasheet view.

Create a Form based on tblContacts; on it put a Subform based on
tblCategoryID; on the Subform put a combo box based on tblCategory,
storing the CatID but displaying the text category.