Contact Mgmt fields

[lmcc007]

What is the best or appropriate way of creating my contact mgmt database
regarding phone numbers? Every example and template I look at all have
separate fields for Business, Busines Fax, Mobile Phone, Home Phone... I'm
trying it with a table (PhoneType and PhoneNumber) and only enter the phone
numbers if availalbe.

I'm wondering am I on the right track or should I do like all the examples
and templates I see? And, another reason I created a PhoneNumbers table
because I would like to be able to search for phone numbers. And, I do want
my form to always display Business, Business Fax... whether there is a phone
number available for the contact person or not.

 

[Tom van Stiphout]

On Tue, 16 Jun 2009 17:23:01 -0700, lmcc007

You are doing it right. Database designers call this "normalization"
which is the process of removing "repeating groups" by spinning off
the data in its own table. The reason some are doing this is for
convenience, but yours is the better approach from a db design
perspective.
For the user interface you need to think of a subform with two
columns. The form is bound to the Phone table, with a dropdown for
PhoneType (gets its rowsource from the PhoneTypes table) and a textbox
for the PhoneNumber value.

-Tom.
Microsoft Access MVP

 

[lmcc007]

Thanks!

I created a form with the three fields:

PhoneNumID (Visible = No)
PhoneTypeID (Combo box)
PhoneNumber (Text box)

Is there a way for the form to always display Business, Business Fax,
Mobile, Home whether there is a phone number availble or not?

I am trying to get the form to look like

Phone numbers-------------------------------------
Business 800-123-4567
Business Fax
Mobile 888-888-1111
Home

I like this standard look.

 

[Tom van Stiphout]

On Tue, 16 Jun 2009 23:26:01 -0700, lmcc007

That's not very easy to do in a Form, but easy in a Report. This is
because doing so would require an Outer Join which will likely make
your form non-updatable (but try it before you give up on this idea).
And you already figured that the other solution of adding 4 records
for each contact on forehand would defeat the purpose of this pretty
database design.

-Tom.
Microsoft Access MVP

 

[lmcc007]

This is because doing so would require an Outer Join which will likely make
your form non-updatable>

I don't know how to do that. I guess I will have to stick with what I have
or do it the old way (you know phone 1, phone 2...) if I want my form to look
a certain way.

 

[Duane Hookom]

I think I have provided a solution in the past to either you or someone much
like you with the same question. You could use four text boxes with the
PhoneTypeID hard-coded in the control source. Then use four subforms that
link to the Primary key of the main form and one of the text boxes.

For instance is your PhoneTypeID is 1 for "Business", add a hidden text box
Name: txtBusiness
Control Source: =1
Then use a single view subform with link values:
Link Master: CustomerID;txtBusiness
Link Child: CustomerID; PhoneTypeID
You can add a label with a caption of "Business"

 

[lmcc007]

I tried it before and couldn't get it to work. Now since I have taken up
some classes, let me go back over my notes and see if I now understand it.
Thanks!

 

[Dale_Fye via AccessMonster.com]

Just a couple of ideas.

I would probably use 5 fields in the table, if it were me.
Contact_Phone_ID (autonumber, PK)
Contact_ID
PhoneNum
PhoneTypeID
PhoneNumber

If you have international contacts, you might want to add a field for the
international code, and maybe for the area code as separate from the phone
number?

Dale

Thanks!

I created a form with the three fields:

PhoneNumID (Visible = No)
PhoneTypeID (Combo box)
PhoneNumber (Text box)

Is there a way for the form to always display Business, Business Fax,
Mobile, Home whether there is a phone number availble or not?

I am trying to get the form to look like

Phone numbers-------------------------------------
Business 800-123-4567
Business Fax
Mobile 888-888-1111
Home

I like this standard look.

You are doing it right. Database designers call this "normalization"
which is the process of removing "repeating groups" by spinning off

[quoted text clipped - 20 lines]

 

[Dale_Fye via AccessMonster.com]

Just a couple of ideas.

I would probably use 5 fields in the table, if it were me.
Contact_Phone_ID (autonumber, PK)
Contact_ID
PhoneNum
PhoneTypeID
PhoneNumber

If you have international contacts, you might want to add a field for the
international code, and maybe for the area code as separate from the phone
number?

Dale

Thanks!

I created a form with the three fields:

PhoneNumID (Visible = No)
PhoneTypeID (Combo box)
PhoneNumber (Text box)

Is there a way for the form to always display Business, Business Fax,
Mobile, Home whether there is a phone number availble or not?

I am trying to get the form to look like

Phone numbers-------------------------------------
Business 800-123-4567
Business Fax
Mobile 888-888-1111
Home

I like this standard look.

You are doing it right. Database designers call this "normalization"
which is the process of removing "repeating groups" by spinning off

[quoted text clipped - 20 lines]

 

[Fred]

We make and use these. I hate to say it, but IMHO separate colums in the
main table is usually the better choice although searchability by phone
number is one of the minuses, as is reduced flexibility to handle people who
have too many phone numbers, or having to put seldom-used columns in to
accomodate seldom used scenerios. (like secondary cell phone) The
upside is that such would make a lot of other things simpler and easier.


IMHO as long as you follow unique definitions of those phone columns, then
such a structure is not un-normalized. (i.e. "Primary Phone" , " Secondary
Phone" , "Primary Cell" "Secondary Cell" "Fax #" are 5 distinct
attributes/definitions)

 

[lmcc007]

This just came to mind: that is create a query that would search on those
fields--that is Primary Phone, Secondary Phone...

Now I need to figure out how to do a form that all I have to do is type a
phone number in and it will find it no matter which field it is store in.

 

[Fred]

Don't mistake me for an expert in this area, (those other guys who answered
know Access 10 times better than me (they'd do better at answering) but
here's a starting point from down on my lower level.

Make a query which puts [EnterPhoneNumber] under each phone number field but
on different lines on the grid. It will generate sql like

SELECT People.PrimaryPhone, People.SecondaryPhone, People.Fax, People.Cell
FROM People
WHERE (((People.PrimaryPhone)=[EnterPhoneNumber])) OR
(((People.SecondaryPhone)=[EnterPhoneNumber])) OR
(((People.Fax)=[EnterPhoneNumber])) OR (((People.Cell)=[EnterPhoneNumber]));

This will make [EnterPhoneNumber] a variable which it will ask the user for
upon opening the query and will return all records where it matches any of
the fields.

You can code the form to do this but for me that takes 15 minutes of head
scratching rather than being something I could just write here.

 

[lmcc007]

Hey Duane,

I went back over your instruction and got it to work. Two problems: 1) I
cannot change the PhoneType, and 2) I cannot tab from PhoneNumber to the next
PhoneNumber, I have to use the mouse.

And, I tried Fred's way "Make a query which puts [EnterPhoneNumber] under
each phone number field but on different lines on the grid." This works too!

Basically, I just want to be able to enter a phone number to find a record,
and have the form look the way I want it to.

I am still thinking about just having a continuous subform that allows me to
select a phone type and enter the phone number. The only reason I am stuck
on this way is normalization. I want to set the database up right.

I tried a couple of off the shelf contact management programs like Act and
they have separate fields for phone numbers. When I searched I had to search
each phone type for a number. Hmmm!

Will I damage the database if I do it with Phone 1, Phone 2, Phone 3...?

 

[lmcc007]

Thanks Fred,

Tried it and it works. Basically, all I want to do is enter a phone number
to pull up a record.

Is there a huge disadvantage doing it this way versus creating a continuous
subform?

I guess you can tell I am stuck on getting that particular look for my form
(you know, Business, Business 2, Business Fax, Mobile).

 

[Dale_Fye via AccessMonster.com]

The big disadvantage of having a separate column for each phone number is the
effort involved when you want to add another category of phone. You must
change your forms, queries, and reports to account for the new field.

With a separate table, you can have a contact with 1 or 10 phone numbers, and
if you decide you want to add another PhoneType category, all you have to do
is add it to your PhoneTypes table.

It takes a while to get used to using subforms, but they really do make the
whole process a lot simpler in the long run.

Dale

This just came to mind: that is create a query that would search on those
fields--that is Primary Phone, Secondary Phone...

Now I need to figure out how to do a form that all I have to do is type a
phone number in and it will find it no matter which field it is store in.

We make and use these. I hate to say it, but IMHO separate colums in the
main table is usually the better choice although searchability by phone

[quoted text clipped - 9 lines]

 

[Fred]

To avoid confusion you really need to think/discuss this as a structure
question and deal with forms afterwards.

There basically two structures discussedin the above thread:

A. A separate phone table with a many-to-one relationship to your people
table as discussed by the other guys

B. Multiple phone columns for multiple phone types in your main people
table as discussed by me.

I listed certain conditions (basically that there are unique meanings &
definitions for each phone number field) under which "B" is an OK choice.
If they are not met, then in my opinion, "A" is the only OK choice.

If we assume that "B" met the conditions for "OK", and it's still in the
running, then it's a matter of choice, and my choice if we were doing it here
would be "B", despite it's downsides, one of which would be to make your
(unusual) described/desired searching job more complex. "B" is probably the
only viable way of meeting your desire of providing the titles and blanks for
phone numbers which have not been entered.

Now you need to decide between "A" and "B"

When you design the likely forms, "A" would have "People" in the main form,
and a "phones" subform. "B" would have a "People" main form (with all of
the phone number types and phone numbers) and no sub-form.

Hope that helps a little.

 

[lmcc007]

Actually a separate table would be best, I just want that form to be
displayed a certain way.

I tried the following code to get it to display the way I want on the form,
but I am not sure I should go this way:

Private Sub PhoneTypeCommandButton_Click()

DoCmd.GoToRecord , , acNewRec
PhoneTypeName = "Business"

DoCmd.GoToRecord , , acNewRec
PhoneTypeName = "Business 2"

DoCmd.GoToRecord , , acNewRec
PhoneTypeName = "Business Fax"

DoCmd.GoToRecord , , acNewRec
PhoneTypeName = "Mobile"

End Sub

I pretty much want to know the correct way of setting up the db. I don't
want to just put it together anyway

The big disadvantage of having a separate column for each phone number is the
effort involved when you want to add another category of phone. You must
change your forms, queries, and reports to account for the new field.

With a separate table, you can have a contact with 1 or 10 phone numbers, and
if you decide you want to add another PhoneType category, all you have to do
is add it to your PhoneTypes table.

It takes a while to get used to using subforms, but they really do make the
whole process a lot simpler in the long run.

Dale

This just came to mind: that is create a query that would search on those
fields--that is Primary Phone, Secondary Phone...

Now I need to figure out how to do a form that all I have to do is type a
phone number in and it will find it no matter which field it is store in.

We make and use these. I hate to say it, but IMHO separate colums in the
main table is usually the better choice although searchability by phone

[quoted text clipped - 9 lines]

 

[lmcc007]

A. would be the choice I think I should choose but I am stuck on what I want
the form to display.