Using check boxes

[pbouk]

Hello,
I am fairly new to Access. I am in the middle of creating a contacts database that contains approximately 3000 + contacts. At least three quarters of these contacts have a number of "Categories" assigned to them. For example "Holiday Cards", "Collector", "Photographer", "Artist".


Many contacts have up to four categories assigned to them, others none.

So far I have two tables: Customer Table One with ID, contact name, and all the address and telephone fields (there are over 40 of these fields).
My second table is the CustomerCategories Table with the ID field again (but I'm concerned this is duplication?).... and all the categories as yes/no check boxes. There are approximately 20 or so of these categories as check boxes.

My first question - does this sound like a good normalised database? Is repeating the ID field in the Categories table a bad idea - should all these categories be in the Customer Table? My concern is that this Table will become tooooo large.

I know it's fairly simple, but it's the best way for my client to keep tabs on its contacts.

I have a second question relates to mailouts. My client will create lists for mailouts derived from the categories. For example: All "Holiday Card" clients will be sent a holiday card. Is the checkbox design a troublesome way to do this?

Thank you for your help.

[Bob Fitz]

IMHO you should have at least three tables:
tblCustomers with fields: CustomerID, FName, LName etc
tblCategories with fields: CategoryID, CategoryName
tblCustomerCategories with fields: CustomerID, CategoryID

If customers have more than one telephone number I would have another table for these.

[pbouk]

Thank you Bob. It's an improvement!
Quick question regarding your suggestion - tblCustomerCategories with fields: CustomerID, CategoryID
Most customers have at least three or four categories. How would I enter more than one category to this table?

I actually have 7 (yes!) address fields with corresponding phone numbers & fax numbers, plus two mobile numbers.

In addition I have a check box for mailing address - ie. all addresses have a check box but only one is checked per contact.
Is this sound design?

Perhaps I could have each address as its own table? And then I could use a combo box that selects one of the seven addresses as the contact's mailing address?
(this is starting to sound like a software fairy story!)

Thank you,
P.

[Bob Fitz]

I have attached a very rough and basic db to illustrate the principles involved.

[pbouk]

Thank you Bob, this is really helpful. I can definitely apply these principles to Categories section of my database.
This might not work for my address fields though ie:

1 Home 1
2 Home 2
3 Home 3
4 Business 1
5 Business 2
6 Business 3
7 Other
8 Mailing Address

The problem here is that one address will be defined in two ways, ie. It will be BOTH the mailing address AND the Home 2 address, say.
I would rather my client did not have to re-enter the mailing address a second time (in order to give it two separate definitions).
And I've read that you cannot run queries (or reports?) from a multi-select list box.

As my client will need to create lists and reports easily from this part of my database, I want this to be as straightforward as possible (ie, it would be easier if every time he wanted a list of say, the mailing address for all Photographers, it did not involve a really complicated query involving all 7 addresses...well, a simple query involving all seven addresses would be okay).

Perhaps something like this would work:
1 Home 1
2 Home 2
3 Home 3
4 Busines 1
5 Business 2
6 Business 3
7 Other
8 Home 1/ Mailing
9 Home 2 Mailing
10 Home 3/ Mailing
11 Business 1 / Mailing

etc

Apart from ease of using the database for reference, it's very important that my client can easily create Reports and Excel lists that include the mailing address.

If you have any further thoughts I'd be very grateful.

P.

[Bob Fitz]

If I have understood your address requirement correctly, you would have a one to many relationship between the Customer table and an address table. The address table would have a one to many relationship to a third table (tblAddressDef) that would hold the addressID and the ID field of a forth table that holds the names of the definitions (Home,Office,Mailing,Other etc)

[pbouk]

Hi Bob,
Can I send on my DB to you? Having a couple of problems with getting the subform to create in the same way as yours does.
I'd rather send this by private email for confidentiality reasons but I can't see an attachment option in the private email section.
Thank you.

[June7]

Can't attach to PMs. Follow instructions at bottom of my post to provide db on forum.