Contact phone number management like in outlook

[dominicg123]

I have a database for our company contacts. Each of of my contacts have several phone numbers. I would like to choose from a combo box, like in outlook the contact phone number that I want to display for earch record. Do you think its possible??

 

[TheAceMan1]

How are ya dominicg123 . . .

Yes its possible! Depends on wether you have a single or seperate field(s) for each number. For [blue]single field[/blue] a query will do, for [blue]multiple fields[/blue] have a look at [blue]union queries[/blue] . . .

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[dominicg123]

I have a single field for type of each number. Maybe my question is not clear... I want to chose to display for each contact only certain type of number. For example , for the contact john smith I want to display is work number and pager number, for Mary Jane I want to display her cell phone and home number etc.. Exactly like you can do in outlook

 

[MajP]

If it was me I would have a seperate table for phone numbers related to your main person table. A single field for each type of number is a non-normalized structure. Not a huge deal, but not a clean design.

tblContactNumbers
fkPersonID (foriegn key to the person table)
strTypePhone
strNumber
blnDisplay (yes/no to display or not)


Lets assume in the main table, Mary is ID number 1 and John is ID 2. Mary has a cell, home, work number. John has a Blackberry, and work number. I want to display Mary's cell and john's home. The data then looks like this.

ID strTypePhone strNumber blnDisplay
1 cell (123)123-456 yes
1 home (999)999-999 no
1 work etc no
2 Blackberry etc no
2 home etc yes

now build your form off of a query where "blnDisplay" equals true. Now the trick will be to ensure that one and only one field is "yes" for each person.

The other modifications could be to have a display number in the main table. Using a little vba, when you choose a number to display("blnDisplay" = true), it updates the display number in the main table.

The normalized table will handle a lot of exceptions such as the person with 2 cell numbers or 2 home number. Also you will not have empty fields for people who do not have a cell or a home phone.