Table design question re duplicate fields

[lmcc007]

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?


Thanks!

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...63f&dg=microsoft.public.access.tablesdbdesign

 

[George]

Dear friend,

Since some of the contacts may have 1 tel. no, some others 2 and so on I
would suggest that you must create a separate table for storing those tel.
nos. This should be related One to Many to your contacts table. You may
have in this table the following fields:

ContactID (which is the primary key of your contact table)
Tel.No
TypeOfTelNo - might be a drop down menu.

I would also advice you that you set as primary keys the first 2 fields in
case your contact have only one tel. no for each tel. category, or in
contrary all the three fields.

Hope this helps,

GeorgeCY

Ο χÏήστης "lmcc007" έγγÏαψε:

 

[Noëlla Gabriël]

Hi,

simply create 2 extra tables, a lookup table similar to:
tlkpPhonetypes
-------------------
fields:
ptID (autonumber) = PK;
ptDescription (text) = Business, private, mobile, .....

and a link table to link the persons to their phone numbers like:

tblPersonsPhoneNr
--------------------
fields:
ppnID (autonumber) = PK;
ppnPerson (long integer or same filed type as the PK of your persons table)
= FK to your table persons;
ppnPhoneType (long integer) = FK to tlkpPhonetypes.ptID;
ppnPhoneNr (text): the field containing the actual phone nr

if usefull you can add a remarks field where you can note things like: "only
use this number from 9 to 5 "

Hope this helps a bit

 

[lmcc007]

Thank you George! Yes, I did this. I'm sorry I posted this twice. I tried
to delete it but I do not see how to delete this extra post.

 

[lmcc007]

Thank you! I am created lookup tables. Oh, I am sorry I posted this twice.
I was not able to figure out how to delete this extra post. For some reason,
my computer is very slow when accessing this discussion group.