Name database with up to six phone numbers per person

[Dan]

I'm looking to create a DB with a person's first, mi and last name and each
person could have from two to six or more locations, phone numbers and
extensions.
Example:
NAME LOCATION TYPE NUMBER
PHONE
-----------------------------------------------------------
joe black richmond phone (123)456-7890 ext 678
fax (123)123-4567
cell (123)987-5432
hopewell phone (123)234-8765
fax (123)786-3456

bill jones salem phone (321)456-1743
cell (321)987-1357

Mike..... etc.

do i need a tblperson, tbllocation, tbltypephone, tblphnumber? Also, any
null information shouldn't print.
Also, how would I create a form to enter the data for each person, since
they may have a different number of phones?
Thanks for any suggestions!

 

[rowiga]

I would do it with two tables. One would store the general information about
the person, the other would store the various phone information. In the main
table, create a ContactID that would be a unique identifier for the person.
In the second table you would have the following fields:

ContactID
Location
NumberType (phone, fax, mobile...)
Number
Extension

You end up with a main form for the general contact information and a
subform for the phone information. These two froms are linked by ContactID.

 

[tina]

i would probably have a minimum of four tables, possibly five. you don't say
whether you need to store information about a person's locations *that is
specific to that person*. for instance, could two people have a location in
Richmond? if so, is it the same Richmond location? or different, such as
each person having a different street address in Richmond?

assuming that a given location is not specific to one user, suggest the
following tables

tblPersons
PersonID (primary key)
FirstName
Lastname
(other fields that describe a specific person)

tblLocations (this is a "supporting" table)
LocationID (primary key)
LocationName
(other fields that describe a specific location)

tblPhoneTypes (this is a "supporting" table)
TypeID (primary key)
TypeName

tblPersonPhones
PersonPhoneID (primary key)
PersonID (foreign key from tblPersons)
LocationID (foreign key from tblLocations)
TypeID (foreign key from tblPhoneTypes)
PhoneNumber
Extension
Comments
(comments isn't necessary, of course, but i often find it handy for notes
about "best time to call", etc)

your data entry can be done from a main form bound to tblPersons, with a
subform bound to tblPersonPhones. the "supporting" tables will be used in
the RowSource of combo boxes on the subform. anytime you can control and
limit data entry in a field to predetermined "acceptable" values, it will
result in 1) more accurate data entry and 2) increased quality in
statistical and "grouping" reports.

hth

 

[Dan]

Thanks, everyone! This is kinda how I was thinking.
I will incorporate your suggestions!