multi phones mess

  • Thread starter Thread starter Dave
  • Start date Start date
D

Dave

I'm trying to setup a database for a church and I have two problems
with the types of numbers in the phone list. The current list has
columns for 4 phone numbers for each person with all the different
types throughout.

Standard (999)000-0000
Extentions ext00000
International 999-0000-000000

First, how should I go about organizing this mess. My first thought
is to have 4 columns of standard numbers with 4 columns of extensions
(extention only)
then 4 columns of international numbers. Is this reasonable or is
there a better solution or is there a way I could write both
international and standard to the same column?

Second, when I print out my reports How would I tell Access that if
there is no standard number then print the international number and if
there is no international number then print "No Number"

Thanks
Dave
 
Dave

Your table structure reminds me of ... a spreadsheet! You've added "sets"
of columns to handle a one-to-many situation (one ?person can have many
phone numbers).

In Access, to get the most out of the features, you need to think and design
relationally (see "normalization" in Access HELP). A more suitable way to
handle the one-to-many in a relational database, like Access, is to use a
table to hold the "many", each as a row. If you will NEVER have the same
phone number shared by more than one ?person, you could use something like:

tblPerson
PersonID
FirstName
LastName
..

trelPhone
PhoneID
PersonID (from tblPerson)
IntlAccessCode
AreaCode
PhoneNumber
Extension
PhoneTypeID

tlkpPhoneType
PhoneTypeID
PhoneType (e.g., landline, toll-free, cell, pager)

This gives you one row in trelPhone per valid combination of ?person and
phone number.

Or have I misunderstood what you are trying to do?
 
Jeff Boyce said:
Dave

Your table structure reminds me of ... a spreadsheet! You've added "sets"
of columns to handle a one-to-many situation (one ?person can have many
phone numbers).

In Access, to get the most out of the features, you need to think and design
relationally (see "normalization" in Access HELP). A more suitable way to
handle the one-to-many in a relational database, like Access, is to use a
table to hold the "many", each as a row. If you will NEVER have the same
phone number shared by more than one ?person, you could use something like:

tblPerson
PersonID
FirstName
LastName
..

trelPhone
PhoneID
PersonID (from tblPerson)
IntlAccessCode
AreaCode
PhoneNumber
Extension
PhoneTypeID

tlkpPhoneType
PhoneTypeID
PhoneType (e.g., landline, toll-free, cell, pager)

This gives you one row in trelPhone per valid combination of ?person and
phone number.

Or have I misunderstood what you are trying to do?
Click to expand...

Please pardon my Access Challenged brain :-)
Actually I think you may have found exactly what I'm looking for.
I'm not real strong with Access and don't really see the complete
outcome in my head and am not completely sure how it will look on the
Data Entry Form.
I'll try to find an example of what you are splaining to me. maybe a
contact manager or something of that sort would have a working
example?

Thanks
Dave
 
Jeff Boyce said:
Dave

Your table structure reminds me of ... a spreadsheet! You've added "sets"
of columns to handle a one-to-many situation (one ?person can have many
phone numbers).

In Access, to get the most out of the features, you need to think and design
relationally (see "normalization" in Access HELP). A more suitable way to
handle the one-to-many in a relational database, like Access, is to use a
table to hold the "many", each as a row. If you will NEVER have the same
phone number shared by more than one ?person, you could use something like:

tblPerson
PersonID
FirstName
LastName
..

trelPhone
PhoneID
PersonID (from tblPerson)
IntlAccessCode
AreaCode
PhoneNumber
Extension
PhoneTypeID

tlkpPhoneType
PhoneTypeID
PhoneType (e.g., landline, toll-free, cell, pager)

This gives you one row in trelPhone per valid combination of ?person and
phone number.

Or have I misunderstood what you are trying to do?
Click to expand...

I have another question now that I look at this. How I be able to
limit in a report the number of phone numbers that are displayed? The
reason I ask is that some of the reports display all and some only
display one or two phone numbers.

Thanks
Dave
 
I have another question now that I look at this. How I be able to
limit in a report the number of phone numbers that are displayed? The
reason I ask is that some of the reports display all and some only
display one or two phone numbers.

Thanks
Dave
Click to expand...

I am not sure that I would normally go quite as far in the direction
of "Relationality" as Jeff - most of my databases do have separate
fields in a "Persons" table for (e.g.) Home Phone, Business Phone,
Mobile Phone - but the answer to your specific question is to Select
the PhoneTypeID(s) you want for the given report.

In fairness to Jeff, too, the "fully relational" structure makes it
very easy to return _a_ telephone number for each person when some of
them only give you a home number, some only give you a work number,
and some only give you a mobile number, etc.. You can also more easily
record and return the individual's "preferred" phone number when
he/she has more than one.

Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher
 
Peter R. Fletcher said:
I am not sure that I would normally go quite as far in the direction
of "Relationality" as Jeff - most of my databases do have separate
fields in a "Persons" table for (e.g.) Home Phone, Business Phone,
Mobile Phone - but the answer to your specific question is to Select
the PhoneTypeID(s) you want for the given report.

In fairness to Jeff, too, the "fully relational" structure makes it
very easy to return _a_ telephone number for each person when some of
them only give you a home number, some only give you a work number,
and some only give you a mobile number, etc.. You can also more easily
record and return the individual's "preferred" phone number when
he/she has more than one.

Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher
Click to expand...


Well, if I might shed just a little more light on the situation. The
Database in question is nothing more than a report spitter outer.
there are maybe 20 different reports that this thing creates and there
really is nobody searching it per say. one person inputs data and
prints out reports for anyone who needs it. I am currently trying to
add some needed functionality to it when I ran across the barage of
phone number styles and thought I might be able to fix it. They have
everything from:
(123)123-1234
(234)345-4567wk x1234
011-1234-123456
(321)321-3214 but only after 6.... and so on all in the same column.
Maybe I should just leave it but it looks aweful to me.

Maybe the best solution is to break it down to Area code, Phone#, and
Ext and leave the formating off of the Phone# so they can put the
International numbers in.

Dave
 
Well, if I might shed just a little more light on the situation. The
Database in question is nothing more than a report spitter outer.
there are maybe 20 different reports that this thing creates and there
really is nobody searching it per say. one person inputs data and
prints out reports for anyone who needs it. I am currently trying to
add some needed functionality to it when I ran across the barage of
phone number styles and thought I might be able to fix it. They have
everything from:
(123)123-1234
(234)345-4567wk x1234
011-1234-123456
(321)321-3214 but only after 6.... and so on all in the same column.
Maybe I should just leave it but it looks aweful to me.

Maybe the best solution is to break it down to Area code, Phone#, and
Ext and leave the formating off of the Phone# so they can put the
International numbers in.

Dave
Click to expand...


You are right - it is awful. That's why getting the data structures
right at the beginning (and enforcing them) is such a vital part of
database design. Unfortunately, you have to start off from where you
are - not from where you would like to be. If you are redesigning, and
do have to deal with international numbers, I would have a separate
field for the international code. You can always have this default to
your local country code and write your report formats to handle local
and international numbers appropriately (i.e. differently).

Migrating your old data to the new table will be a headache, but I
think the project needs to be done.

Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher
 
Back
Top