Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Database Design / January 2005

Tip: Looking for answers? Try searching our database.

Name database with up to six phone numbers per person

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Dan - 27 Jan 2005 20:31 GMT
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 - 27 Jan 2005 20:53 GMT
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.

> 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
[quoted text clipped - 19 lines]
> they may have a different number of phones?
> Thanks for any suggestions!
tina - 27 Jan 2005 22:45 GMT
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

> 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
[quoted text clipped - 19 lines]
> they may have a different number of phones?
> Thanks for any suggestions!
Dan - 28 Jan 2005 21:27 GMT
Thanks, everyone! This is kinda how I was thinking.
I will incorporate your suggestions!

> 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
[quoted text clipped - 64 lines]
> > they may have a different number of phones?
> > Thanks for any suggestions!
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.