Table design question re duplicates fields

I am using Access 2007. I am trying to create a job searching database. The
problem I am having is the phone number field. Sometimes there may be six
different phone numbers. In all the examples I have looked at, they have all
separate fields—that is, Work Phone, Home Phone, Car Phone, and so on. Is
there a better way to set this up? Is there a way to save data from four
different fields into one field? I have the following fields:

PhoneType1 = Business
Phone1
PhoneType2 = Business 2
Phone2
PhoneType3 = Business Fax
Phone3
PhoneType4 = Mobile
Phone4

As you can see above that Phone type is repeated over and over again.

There should be no duplicates phone numbers for a company.

The phone numbers needs to be in one column in order to sort, filter, and
find.

Also, I want the four PhoneType fields to always be displayed on my form
until I decide to change the type it.

Any suggestions?

On Thu, 7 May 2009 10:37:01 -0700, lmcc007
wrote:

I am using Access 2007. I am trying to create a job searching database. The
problem I am having is the phone number field. Sometimes there may be six
different phone numbers. In all the examples I have looked at, they have all
separate fields—that is, Work Phone, Home Phone, Car Phone, and so on. Is
there a better way to set this up? Is there a way to save data from four
different fields into one field? I have the following fields:

PhoneType1 = Business
Phone1
PhoneType2 = Business 2
Phone2
PhoneType3 = Business Fax
Phone3
PhoneType4 = Mobile
Phone4

As you can see above that Phone type is repeated over and over again.

There should be no duplicates phone numbers for a company.

The phone numbers needs to be in one column in order to sort, filter, and
find.

Also, I want the four PhoneType fields to always be displayed on my form
until I decide to change the type it.

Any suggestions?


You're using a relational database: use it relationally!

One Company has multiple Phones: a one to many relationship. Create a *second
table*, Phones, with fields

PhoneID: Autonumber primary key
CompanyID: foreign key to your current table's primary key
PhoneType: Text, "Business", "Home", etc. etc., probably from a very small one
field PhoneTypes table
Phone: Text, the actual phone number

You can create a unique two-field Index on CompanyID and Phone to prevent
duplicates.

I question whether you might need multiple Contacts (people with whom to
communicate) at each Company though, and have the phone table linked to
Contacts rather than to Companies.
--

John W. Vinson [MVP]

Yes, I did just that. Company and PhoneNumber are my primary keys.

The problem is I want my form to always display Business, Business 2,
Business Fax, and Mobile with all the values being stored in the PhoneNumber
field.

Contacts -- I am not sure about that because the people I contact are inside
the company--like, human resource manager, secretary, supervisor... And,
their addresses are usually the same except with very big companies like
Exxon, Shell...

"John W. Vinson" wrote:

On Thu, 7 May 2009 10:37:01 -0700, lmcc007
wrote:

I am using Access 2007. I am trying to create a job searching database. The
problem I am having is the phone number field. Sometimes there may be six
different phone numbers. In all the examples I have looked at, they have all
separate fields—that is, Work Phone, Home Phone, Car Phone, and so on. Is
there a better way to set this up? Is there a way to save data from four
different fields into one field? I have the following fields:

PhoneType1 = Business
Phone1
PhoneType2 = Business 2
Phone2
PhoneType3 = Business Fax
Phone3
PhoneType4 = Mobile
Phone4

As you can see above that Phone type is repeated over and over again.

There should be no duplicates phone numbers for a company.

The phone numbers needs to be in one column in order to sort, filter, and
find.

Also, I want the four PhoneType fields to always be displayed on my form
until I decide to change the type it.

Any suggestions?


You're using a relational database: use it relationally!

One Company has multiple Phones: a one to many relationship. Create a *second
table*, Phones, with fields

PhoneID: Autonumber primary key
CompanyID: foreign key to your current table's primary key
PhoneType: Text, "Business", "Home", etc. etc., probably from a very small one
field PhoneTypes table
Phone: Text, the actual phone number

You can create a unique two-field Index on CompanyID and Phone to prevent
duplicates.

I question whether you might need multiple Contacts (people with whom to
communicate) at each Company though, and have the phone table linked to
Contacts rather than to Companies.
--

John W. Vinson [MVP]

On Thu, 7 May 2009 16:09:01 -0700, lmcc007
wrote:

Yes, I did just that. Company and PhoneNumber are my primary keys.

The problem is I want my form to always display Business, Business 2,
Business Fax, and Mobile with all the values being stored in the PhoneNumber
field.

I'm not sure I understand. You'ld use a Subform based on Phones on the form,
displaying the phone number and the phone type fields. What do you mean by
"all the values being stored in the PhoneNumber field"? There should only be
one.

Contacts -- I am not sure about that because the people I contact are inside
the company--like, human resource manager, secretary, supervisor... And,
their addresses are usually the same except with very big companies like
Exxon, Shell...

So? They might all have the same ADDRESS but surely they each have their own
phone. I have a set of tables

CONtblPeople
ContactID
LastName
FirstName
etc

CONtblAddresses
AddressID autonumber PK
ContactID
Address1
Direction
Street
Suffix ' e.g. St., Ave., Blvd.
City
State ' also used for Province, etc.
PostCode

CONtblPhones
PhoneID autonumber PK
ContactID
PhoneType
Phone

--

John W. Vinson [MVP]

The problem is I want my form to always display Business, Business 2,
Business Fax, and Mobile with all the values being stored in the PhoneNumber
field.

I'm not sure I understand. You'ld use a Subform based on Phones on the form,
displaying the phone number and the phone type fields. What do you mean by
"all the values being stored in the PhoneNumber field"? There should only be
one.

I want my form to look like Outlook Contact Manager form. You know:

Phone Numbers
Business 800-833-1212
Business 2
Business Fax 800-833-1213
Mobile

Using the subform it will only display if there is data and if no data it
will not display Business, Business 2...

I am reading Access Basics for Programming by Crystal I got off of Allen
Browne's website. I haven't finished yet but maybe it will be address here.

Thanks!


"John W. Vinson" wrote:

On Thu, 7 May 2009 16:09:01 -0700, lmcc007
wrote:

Yes, I did just that. Company and PhoneNumber are my primary keys.

The problem is I want my form to always display Business, Business 2,
Business Fax, and Mobile with all the values being stored in the PhoneNumber
field.

I'm not sure I understand. You'ld use a Subform based on Phones on the form,
displaying the phone number and the phone type fields. What do you mean by
"all the values being stored in the PhoneNumber field"? There should only be
one.

Contacts -- I am not sure about that because the people I contact are inside
the company--like, human resource manager, secretary, supervisor... And,
their addresses are usually the same except with very big companies like
Exxon, Shell...

So? They might all have the same ADDRESS but surely they each have their own
phone. I have a set of tables

CONtblPeople
ContactID
LastName
FirstName
etc

CONtblAddresses
AddressID autonumber PK
ContactID
Address1
Direction
Street
Suffix ' e.g. St., Ave., Blvd.
City
State ' also used for Province, etc.
PostCode

CONtblPhones
PhoneID autonumber PK
ContactID
PhoneType
Phone

--

John W. Vinson [MVP]

On Thu, 7 May 2009 18:02:06 -0700, lmcc007
wrote:

I'm not sure I understand. You'ld use a Subform based on Phones on the form,
displaying the phone number and the phone type fields. What do you mean by
"all the values being stored in the PhoneNumber field"? There should only be
one.

I want my form to look like Outlook Contact Manager form. You know:

Phone Numbers
Business 800-833-1212
Business 2
Business Fax 800-833-1213
Mobile

Using the subform it will only display if there is data and if no data it
will not display Business, Business 2...

Ah, ok.

Base the subform not on Phones but on a Query left joining PhoneTypes to
Phones:

SELECT PhoneTypes.PhoneType, Phones.PhoneType, Phones.Phones
FROM PhoneTypes LEFT JOIN Phones
ON PhoneTypes.PhoneType = Phones.PhoneType
ORDER BY PhoneTypes.PhoneType;

This will display all types whether or not there's a phone to match. You do
need to include the PhoneType field from both tables in order to have the
query updateable, but you needn't display it in the subform.
--

John W. Vinson [MVP]