table design question (1 Viewer)

[b9791]

I am combining multiple types of Mailing Lists into one single table and using checkboxes to specify the list type. In addition, I have separated out businesses from contacts so that I can eliminate the duplicate data. My question is this; Since some of my contacts don't necessarily have a business name, how do I allow for this problem in the database? Does the contacts table need to have address fields, or is there some other way that I can do this?

I think I may know, but would still appreciate an answer from someone who has either done somthing similar or knows what I am trying to do.

Thank you in advance,

Brian

 

[llkhoutx]

I use the following solution:

Individual names are stored in txtFirstName, txtMiddleName, txtLastName, which I concantenate into txtName=txtLastName & ", " & txtFirstName & " " & txtMiddleName.

Business names are stored directly into txtName.

This way all names sort correctly. I have a checkbox on my form which hides/displays txtFirstName, etc. and a small button to concantenate and store the individual name into txtName.

This solution is complicated by individual names. Not everyone has a middle name, some require a name suffix (Jr., Sr., etc.), some have a prefix (Dr., etc.). You can make the name generator as robust as you require.

This ought to get you started.

 

[b9791]

I see what you have done, but I don't see how that keeps the integrity of a datbase. If Company A has 3 contacts, I still only want Company A to have 1 company entry in the database with 3 contacts. If I follow your method I will have 3 Company A's and 3 contacts. I really want to have 1 Company and 1 primary contact and then an infinite number of secondary contacts.

Brian

 

[llkhoutx]

Keep contacts in a separate table linked to the primary table by an ID. Contact table has a boolean field indicating primary contact.

 

[b9791]

What about residential contact that has no business info? Does the boolean field take care fo that? (I have never used a boolean number type before)

 

[llkhoutx]

A contact is a contact. If primary contacts, boolean=true.

 

[b9791]

I get it now, I misread it. Thank you for your help.