Design question re duplicates & normalization

[lmcc007]

I am using Access 2007. I am trying to create a job searching database. The problem I am having is with the phone number field. Sometimes there may be six different phone numbers. I looked at Microsoft contact management templates and the templates or database examples have separate fields -- that is, Work Phone, Home Phone, Car Phone, and so on. Is there a better way to set this up? Is there a way to save data from four different fields into one field? I have the following fields:

PhoneType1 = Business
Phone1
PhoneType2 = Business 2
Phone2
PhoneType3 = Business Fax
Phone3
PhoneType4 = Mobile
Phone4

As you can see above that Phone type is repeated over and over again.

There should be no duplicates phone numbers for a company.

The phone numbers needs to be in one column in order to sort, filter, and find.

Also, I want the four PhoneType fields to always be displayed on my form until I decide to change the type it.

Any suggestions?

 

[dhookom]

You could create a related "contact phone" table that has one record per contact per phone number. This would be maintained using a continuous subform on the contact main form.

Duane
Hook'D on Access
MS Access MVP

 

[lmcc007]

Thanks Duane, but I tried that already. And, I made the CompanyID and PhoneNumber fields as primary keys, which solve the duplicate phone number problem. The problem I face is that I want my form to have Business, Business 2, Business Fax, and Mobile displayed always. I want it to work look like the Phone Numbers does in Outlook Contact Manager form. I have been playing with it for over a week now, but it is not working.

And, I wanted to know if there is a reason MS set their database up with separate fields instead of putting the phone numbers in a separate table.

 

[dhookom]

Their sample isn't normalized. There is some trade-off in functionality, user interface, and what users will understand. You could display all phone numbers if you place 4 text boxes on your form with control sources like:
="Business"
="Business 2"
etc
Then add four small subforms with the link master child to the CompanyID and each of the 4 new text boxes.

Duane
Hook'D on Access
MS Access MVP

 

[lmcc007]

Their sample isn't normalized." I wonder why since this same information are published in their books and they stress normalizing your data.


Okay, I am trying what you suggested but so far I am getting the same results as before. That is, phone numbers in four seperate fields (Business, Business2, Business Fax, and Mobile). But if I want to sort, search, and so on a particular phone number, I will have go to each field. That is not what I want to have to do. And, I did not want duplicate phone numbers for companies.

 

[PHV]

Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

You haven't replied with your table structure or how you set up your form and subform. This is critical information if you want help.

Duane
Hook'D on Access
MS Access MVP

 

[lmcc007]

I am trying to create a form that looks like the Outlook Contact Manager’s form. For example:

Phone Numbers---------------------------------
Business 800-555-1212
Business 2
Business Fax 800-555-1213
Mobile

I want the form to always display Business, Business 2, Business Fax, and Mobile whether there is a phone number in the field or not. And, if I need to add additional phone numbers I can. Below is what I have created:

tblCompanies
CompanyID AutoNumber (PK)
CompanyName Text (Indexed, No Duplicates)


tblPhoneNumbers
PhoneNumberID AutoNumber (PK)
PhoneType Text
Indexed = Yes (Duplicates OK)
Lookup: Display Control = Combo Box
Row Source Type = Table/Query
Row Source = SELECT [tblPhoneTypes].
[PhoneType],
[tblPhoneTypes].[Seq]
FROM tblPhoneTypes
ORDER BY [Seq];
Bound Column 1
Column Count 2
Phone Text (20)
Input Mask = !999\-000\-0000;0;_
Allow Zero Length = Yes
Indexed = Yes (No Duplicates)



tblPhoneTypes
PhoneType Text (PK)
Seq Number (Long Integer)



Query1
SELECT tblPhoneTypes.PhoneType, tblPhoneNumbers.PhoneType, tblPhoneNumbers.Phone, tblPhoneNumbers.CompanyID
FROM tblPhoneTypes LEFT JOIN tblPhoneNumbers ON tblPhoneTypes.PhoneType = tblPhoneNumbers.PhoneType
ORDER BY tblPhoneTypes.Seq;



frmCompanies
Text Box = CompanyName
Control Source = CompanyName

Query1 subform
Source Object = Query1 subform
Link MasterFieds = CompanyID
Link Child Fields = Company ID



Relationships
tblPhoneTypestblPhoneNumbers
tblPhoneTypes tblPhoneNumbers
PhoneType PhoneType

Attributes: Not Enforced
RelationshipType: One-To-Many

tblCompaniestblPhoneNumbers
tblCompanies tblPhoneNumbers
CompanyID 1 Many CompanyID

Attributes: Enforced, Cascade Deletes
RelationshipType: One-To-Many

 

[dhookom]

You are missing the CompanyID in tblPhoneNumbers. I would create a unique index on CompanyID and PhoneType in tblPhoneNumbers.

As I stated, you can create a small subform based on tblPhoneNumbers with only the Phone field bound to a text box.

Add this subform four times to frmCompanies. Add four text boxes to this form:
[tt][blue]
Name: txtBusiness
Control Source: ="Business" (value from tblPhoneTypes)
Enabled: No
Locked: Yes

Name: txtBusiness2
Control Source: ="Business 2" (value from tblPhoneTypes)
Enabled: No
Locked: Yes

Name: txtBusinessFax
Control Source: ="Business Fax" (value from tblPhoneTypes)
Enabled: No
Locked: Yes

Name: txtMobile
Control Source: ="Mobile" (value from tblPhoneTypes)
Enabled: No
Locked: Yes
[/blue][/tt]

Then set the link master/child for the four subforms to
[tt][blue]
Link Child: CustomerID;PhoneType
Link Master: CustomerID;txtBusiness (or txtBusiness2 or txtBusinessFax or txtMobile)
[/blue][/tt]

This will always display the appropriate values for each of the four "hard-coded" phone types. You could add another subform for misc other phone types that aren't part of the original four.

Duane
Hook'D on Access
MS Access MVP

 

[lmcc007]

<You are missing the CompanyID in tblPhoneNumbers.>

Thanks for pointing that out. I forgot to add that line when typing. I rechecked my tblPhoneNumbers table and it's there.

I will try what you suggested and get back with you.


Thanks a bunch!!!

 

[lmcc007]

Hi Duan,

I am working on the subform and can't get it to work. I tried it through the Wizard and then creating a form and dragging it onto the main form, but it is not working.

Don't understand this: <you can create a small subform based on tblPhoneNumbers with only the Phone field bound to a text box. Add this subform four times to frmCompanies. Add four text boxes to this form:>

1) Open frmCompanies in design view.

2) Hit subform/subreport

3) Drag mouse form

4) Use existing tables and queries

5) Next

6) Tables: tblPhoneNumbers

7) Availalbe Fields: Phone

8) Next

9) Show tblPhoneNumbers for each record in tblCompanies
using CompanyID

10) Next

11) tblPhoneNumbers subform2

12) Finish

13) Repeated 3 more times

14) Added text boxes

15) Clicked Form View, all the phone are the same in each box.

<with only the Phone field bound to a text box.> I don't see where to bound Phone field to the text boxes.

I don't know what is going on--I'm lost.

 

[dhookom]

Did you create the four text boxes as I described with the appropriate names and control sources?

I created the subform first and then dragged it from the database window onto the design view of the main company form. Then set the link master childs for the first subform to the fields as I suggested. Each of the link master properties such match the CompanyID and one of the text boxes. The text boxes will make sure the subform has the appropriate phonetype value.

Duane
Hook'D on Access
MS Access MVP

 

[lmcc007]

I redid everything. Now I keep getting a parameter error. That is,

Enter Parameter Value

txtBusiness

OK



Property Sheet displays:

Selection type: Subform/Subreport

tblPhoneNumbers


Data tab:

Source Object tblPhoneNumbers
Link Master Fields CompanyID;txtBusiness
Link Child Fields CompanyID;PhoneType
Filter On Empty Master Yes
Enabled Yes
Locked No


Then when I changed Link Master Fields to: frm4txtboxes.txtBusiness, I don't get the parameter error message but the phone number field is blank even though there are phone numbers.

 

[dhookom]

The subforms are on the form. Did you create the four text boxes? Did you set the Control Sources of the text boxes to match the values in your phonetype table?

Do you understand what my suggestion is trying to do? All this depends on is the link master/child properties of the main form and subform.

Duane
Hook'D on Access
MS Access MVP

 

[lmcc007]

<The subforms are on the form. Did you create the four text boxes? Did you set the Control Sources of the text boxes to match the values in your phonetype table?>

I drag the text boxes on the form instead of copying and pasting. When I did that it created a subform. After copying and pasting them it worked.

<Do you understand what my suggestion is trying to do? All this depends on is the link master/child properties of the main form and subform.> No I do not, I wish I did so I can understand it more.

When I hit Tab in the phone field it clears the field and I can't enter the next phone number or go to the next phone number. Do I have to create some code to tell it to go to next field?

Thanks, I do appreciate all your help because I just could not figure it out. I am still learning the basis. It just gets so frustrating when your mind is set on one thing.

 

[dhookom]

I don't know how to better explain how subforms work in terms of keeping their link master/child fields synchronized. Most of the apps you might see use a single field to link the subform with the main form. I am suggesting you use two linking fields/values.

You might need to set the cycle property of the subforms so they don't add new records other than the one related phone number you want to store for each type.



Duane
Hook'D on Access
MS Access MVP

 

[lmcc007]

After working on this so much my mind is in a mumble jumble. I am stilling reading Access Basics for Programming... by Crystal.

Do you think I am taking the right approach on setting up this job searching database? ARe would you have set your phone numbers field up differently?

 

[lmcc007]

<I would create a unique index on CompanyID and PhoneType in tblPhoneNumbers.>

I tried doing this in Table Design View but couldn't--it did not work correctly. But when I copied and created another table and made CompanyID and PhoneType primary keys, it worked. Meaning, I could not enter Shell and Business twice.

Was I supposed to have done something different?

 

[dhookom]

I think you are finding out why the sample was un-normalized. To me the better alternative is to have a continuous subform that allows users to select a phone type and enter the phone number.

Crystal's tutorials and youtube videos are a good place to start learning Access.

Duane
Hook'D on Access
MS Access MVP

 

[lmcc007]

<To me the better alternative is to have a continuous subform that allows users to select a phone type and enter the phone number.>

Actually, I started off that way but I could not get the look I wanted. And through many tries I got side track. I did the following:

1. made PhoneType a combo box on the form and

2. PhoneNumber in a continuous subform.

3. Problems encountered:

A. I couldn't figure out how to get the PhoneType to
stick or glue to the PhoneNumber.

B. If I later found out that the business number
was actually the fax number, I had to reenter the
phone number.

I guess I am trying to say there were not
synchronized.