MS Access: How to group records/addresses by category/categories

W

wattog

I am new to Access (2002), and am reading up on it in two books. I have
also checked the online help, but can't find an answer to what I would think
would be a very basic question about categories or groups of records.

I plan to put together a MASTER address/contact list for use in a new job,
but I need to be able to group the individual records in multiple categories.
In other words, I will want to generate reports with all addresses in a
certain group, but some of the records/people from the master list will
appear in more than one group. It would be like having a master address
book, and wanting to "code" or designate a person as BOTH a business contact
and a friend contact. (Although in my case, it will coding/grouping contacts
for different committees and different events, with some individuals included
in not just one group, but several groups.

I can't figure out whether I need a different table for each group, or
whether I can code records in a table for multiple categories. If I do use
different tables for each group, how do I connect the same person who appears
in several groups, so that I will make changes to only one record that will
be reflected across all instances of that record in other tables.

I'm sorry if I am not explaining this very well!

If you have a book or online source that I need to read ... please let me
know.

Thanks!
 
J

John Vinson

I can't figure out whether I need a different table for each group, or
whether I can code records in a table for multiple categories. If I do use
different tables for each group, how do I connect the same person who appears
in several groups, so that I will make changes to only one record that will
be reflected across all instances of that record in other tables.

If you have a "many to many" relationship between Contacts and
Categories, the proper way to structure your tables is to have *three*
tables:

Contacts
ContactID <Primary Key>
FirstName
LastName
< etc etc >

Categories
Category Text <Primary Key>

ContactCategories
ContactID <link to Contacts>
Category <link to Categories>

Thus if a contact is in five categories, there would be five records
in ContactCategories for that contact; if you have 318 contacts in the
"Prospective Customer" category, there'd be 318 records in
ContactCategories, each with a different ContactID.

You could then create a Query joining Contacts to ContactCategories
with a criterion on Category to find all contacts in a particular
category.

John W. Vinson[MVP]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top