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]

If you have a continuous subform and it doesn't work, you should provide:
Record Source:
Controls on the subform:
Link Master/Child:

Your subform wouldn't be much different than the order details subform in northwind sample mdb. Your order is the company, the phone type is the product, and the phone number is the quantity.

Duane
Hook'D on Access
MS Access MVP

 

[lmcc007]

Okay Duane, I am not following you here. Let me down load Northwind again and try to see if I understand what you said.

But in th meantime, what I am trying to say is that if I move the phone number down a row, I want the phone type to automatically move with it. For example, I initially entered:

Row# PhoneType PhoneNumber
1 Assistant 800-888-9999
2 Business 877-123-4569
3 Business2 801-456-7890

Now I want Assistant to be the last entry. To do that I had to retype everything because if I change the type, it would look like this:

Row# PhoneType PhoneNumber
1 Business 800-888-9999
2 Business 877-123-4569
3 Business2 801-456-7890

Instead of looking this this:

Row# PhoneType PhoneNumber
1 Business 877-123-4569
2 Business2 801-456-7890
3 Assistant 800-888-9999

 

[dhookom]

You can add the phone type table to the record source of your subform so that the sequence can be used for sorting. If you add more records at the bottom of the subform, yuou will need to requery the subform if you want to put them in order.

Duane
Hook'D on Access
MS Access MVP

 

[lmcc007]

Okay, I created another database as you suggested--see below:

DocumentTables

tblCompanies
CompanyID dbLong PrimaryKey Indexed
CompanyName dbText Indexed

tblPhoneNumbers
PhoneNumberID dbLong PrimaryKey Indexed
CompanyName dbLong ForiegnKey Indexed
PhoneType dbLong
PhoneNumber dbText
PhoneExt dbText

tblPhoneType
PhoneTypeID dbLong PrimaryKey Indexed
PhoneType dbText Indexed



DocumentRelations

Name: Reference1
Table: tblPhoneType
Foreign Table: tblPhoneNumbers
PK: PhoneTypeID FKhoneType

Name: tblCompaniestblPhoneNumbers
Table: tblCompanies
Foreign Table: tblPhoneNumbers
PK: CompanyID FK:CompanyName

<If you have a continuous subform and it doesn't work, you should provide:Record Source:Controls on the subform:Link Master/Child:>

I don't understand this.

<Your subform wouldn't be much different than the order details subform in northwind sample mdb. Your order is the company, the phone type is the product, and the phone number is the quantity.>

As I am examining the Northwind Traders I see they have Code behind their forms. Is that why their form works differently?

I do not understand the code, I wish I did, but here it is:

Private Sub ProductID_BeforeUpdate(Cancel As Integer)
' If Orders Subform is opened as a standalone form, display a message
' and undo changes made to the ProductName text box. (The IsLoaded function
' finds a form in the Forms collection when it's opened as a standalone
' form, but not when it's opened as a subform on a main form.)

Dim strMsg As String, strTitle As String
Dim intStyle As Integer

If IsLoaded("Orders Subform") Then
strMsg = "You can't add or edit a Product Name when you open Orders Subform as a standalone form."
intStyle = vbOKOnly
strTitle = "Can't Add or Change Product Name"
MsgBox strMsg, intStyle, strTitle
Me!ProductID.Undo
Me.Undo
End If

End Sub


Private Sub ProductID_AfterUpdate()
On Error GoTo Err_ProductID_AfterUpdate

Dim strFilter As String

' Evaluate filter before it's passed to DLookup function.
strFilter = "ProductID = " & Me!ProductID

' Look up product's unit price and assign it to UnitPrice control.
Me!UnitPrice = DLookup("UnitPrice", "Products", strFilter)

Exit_ProductID_AfterUpdate:
Exit Sub

Err_ProductID_AfterUpdate:
MsgBox Err.Description
Resume Exit_ProductID_AfterUpdate

End Sub


Yes, I want my PhoneType and PhoneNumber fields to do like the Product and United Price fields in Northwind db.

 

[dhookom]

Every form and control in Access have properties. Do you know where to find these? I am simply asking you to provide some of the significant properties.

You don't need any of that code. I simply wanted you to create a subform based on your tblPhoneNumbers with a combo box to select the phone type and a text box to enter the phone number. Can you do that?

Duane
Hook'D on Access
MS Access MVP

 

[lmcc007]

<Every form and control in Access have properties. Do you know where to find these?>

Yes, from the Property Sheet.


<I am simply asking you to provide some of the significant properties.>

I don't understand.


<You don't need any of that code.>

Oh, okay, because I was trying to figure out what that code meant.


<I simply wanted you to create a subform based on your tblPhoneNumbers with a combo box to select the phone type and a text box to enter the phone number. Can you do that?>

I did do that. Well, I thought. Here it is:

Created a form and called it frmPhoneNumbers_Subform

frmPhoneNumbers_Subform
Combo Box = PhoneType
Control Source = PhoneType
Row Source = SELECT [tblPhoneTypes].PhoneTypeID,
[tblPhoneTypes].PhoneType FROM
tblPhoneTypes ORDER BY [PhoneType];
Row Source Type = Table/Query

Text Box = PhoneNumber
Control Source = PhoneNumber

Text Box = PhoneExt
Control Source = PhoneExt

Dragged frmPhoneNumbers_Subform to frmCompanies_main

Clicked Property Sheet and the Selection Type is Subform/Subreport

Source Object = frmPhoneNumbers_subform
Link Master Fields = CompanyID
Link Child Fields = CompanyName
Filter on Empty Master = Yes
Enable = Yes
Lock = No

 

[dhookom]

<I am simply asking you to provide some of the significant properties.>

I don't understand

But you did provide the properties for us.

What about the subform of telephone numbers isn't what you expect or want?

Duane
Hook'D on Access
MS Access MVP

 

[lmcc007]

I want my PhoneType and PhoneNumber fields to do like the Product and Unit Price fields in Northwind db.

When I removed the DLookup code in Northwind, there form did what my form does. Meaning, if you select another Product, the Unit Price does not change.

 

[dhookom]

What do you want to behave like the Unit Price? There is not any field in your phone number or phone type to behave like the Unit Price.

Duane
Hook'D on Access
MS Access MVP

 

[lmcc007]

<What do you want to behave like the Unit Price? There is not any field in your phone number or phone type to behave like the Unit Price.>

If I open the form and decide that I want the Assistant's phone number to be the first row in the PhoneNumbers_subform instead of the fourth row, I want the phone number field to travel with the assistant field.

A lot of the instructions I don't understand, so I decided to take some courses and then, hopefully, I will understand some of the lingo used. For example, I didn't understand the below:

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

So, I assume you meant to set CompanyID as the PK and PhoneType as the second PK.

In the past I relied on templates and created dbs I really didn't understand. This time I want to do everything from scratch and know what I am doing and why.

Anyway, I bought some Access courses from 599CD.com that I am now taking. I just finish with Access 101 and getting ready to start another course. So far so good. Getting that foundation (tables) down is very, very important.

 

[dhookom]

If you want to be able to sort by one phone type for one company and another phone type for a different company then you will need to add a field in the tblPhoneNumbers table that stores the order for sorting.

Creating a unique index can be on one or more fields combined. It doesn't necessarily have to be the primary key. A primary key field or fields is unique but you can set up additional unique fields in your table to prevent duplicates. My suggestion on the unique index was to prevent a company from having two of the same PhoneType records. If you want to allow more than one Assistant then you would not want to create a unique index on the company and phone type fields in tblPhoneNumbers.

Duane
Hook'D on Access
MS Access MVP

 

[lmcc007]

<If you want to be able to sort by one phone type for one company and another phone type for a different company then you will need to add a field in the tblPhoneNumbers table that stores the order for sorting.>

That's too advanced--don't understand--I have to have very basic instructions for a dummy.


<Creating a unique index can be on one or more fields combined.>

Okay, I tried but I couldn't do it; that's why I used the PKs.


After I finished these courses I am pretty I will understand things more.

 

[dhookom]

Please just answer, do you want to be able to sort by one phone type for one company and another phone type for another company? This would be very unusual and probably not a good idea but you are leaving us guessing as to what you really want.

Regarding the indexing have you opened the index dialog box in the design view of a table? If not, try it so you can see the fields, index names, and index types. Press F1 to find out more about creating indexes.

Duane
Hook'D on Access
MS Access MVP

 

[lmcc007]

<Please just answer, do you want to be able to sort by one phone type for one company and another phone type for another company?>

Yes.

 

[dhookom]

Do you have a good reason for this? Do you realize that if you want to sort differently by phone type then you have to store something in the table that allows you to identify the sorting?


Duane
Hook'D on Access
MS Access MVP

 

[lmcc007]

<Do you have a good reason for this?>

Not really. Maybe you're right I don't need it.


<Do you realize that if you want to sort differently by phone type then you have to store something in the table that allows you to identify the sorting?>

No.

 

[dhookom]

All you have to do is look at your tables and if you can't find a column or columns that you can sort by then you can't sort the records.

Duane
Hook'D on Access
MS Access MVP

 

[lmcc007]

Okay, got it. Thanks for all your help!!

 

[lmcc007]

<A primary key field or fields is unique but you can set up additional unique fields in your table to prevent duplicates. My suggestion on the unique index was to prevent a company from having two of the same PhoneType records. If you want to allow more than one Assistant then you would not want to create a unique index on the company and phone type fields in tblPhoneNumbers.>

Okay, finally got it. So it's best to use unique index instead of haveing PK1, PK2, Pk3... when I do not want to have duplicate phone numbers for a company. Meaning, I created a unique index for PhoneType, PhoneNumber, and PhoneExtension.

Is there a reason why not to use PKs?

 

[dhookom]

Technically a table can have only one primary key. I usually add an Autonumber primary key to every table.

You haven't really addressed my statement regarding a company having multiple assistant phone numbers. You need to decide what should be allowed or not and set the unique indexes to enforce your decision.

Duane
Hook'D on Access
MS Access MVP