How do i create a Clienttable which have support for multiple contact information details?

[HiTechCoach]

You new design is getting less normalized (worse) not better. It looks more like a spreadsheet or report than a well normalized relational database.

Phones are phone number are phone numbers. There are different types and/or categories you can use to help identify the one you want. Splitting phone numbers into a two tables, land line and mobile, would be like splitting people into two tables by sex. Sex is a property of people just like mobile/land line is a property for phone numbers.

Properties are data that help define the object.

[ThornofSouls]

So how can i make the design more normalized with the table design and relations?

Would it be okay if i shared the db i'm currently building for a opinion and feedback?

[HiTechCoach]

So how can i make the design more normalized with the table design and relations?

Learning to normalized data takes practice. Learning what NOT to do is a lot of it.

You were getting close with your original design. The two phone number tables needed to be combined.


tblClientPhoneNumbers

• ClientID (FK)
• PhoneID (PK)
• PhoneNumber (text)
• Type (would include mobile types) (foreign key (FK) to lookup table)

tblClientEmails

• ClientID
• EmailID
• Email (text)
• Type (foreign key (FK) to lookup table)

If you look at the fields in the two about table the structure is identical. Just like the two phone number table were identical. That should tell ytu they can (I believe should) be combined into a single table.


You would end up with this:

tblClientContacts

• ClientID
• ClientConactID (PK)
• ContactData (text)
• ContactTypeID (foreign key (FK) to lookup table)

tblLookupContactTypes

• ContactTypeID (PK)
• ContactTypeDesc (Work Phne, Work Fax, work email, home phone, home email, etc.)
• ContactTypeCategory (E = email, P = Phone, M = mobile)

You can now create a query to pull all the work contacts or email, or whatever combination you need.

The following are indicators that you are getting a more normalized design:

1) If you ever need a new contact method that you simply add a type to the tblLookupContactTypes.

**** A poor design would require you to make table design changes.

2) Search for a phone number or email requires searching a single field in a single table.

**** A poor design would require you to make multiple queries for different tables and/or search multiple fields in the same record.

Would it be okay if i shared the db i'm currently building for a opinion and feedback?

Yes, that would be very helpful.

[ssanfu]

Small change:

tblClientPhoneNumbers

Type (would include mobile types) (foreign key (FK) to lookup table)

"Type" is a reserved word. I would use "PhNumType"




tblClientEmails

Type (foreign key (FK) to lookup table)

I would use "EmailType"

[HiTechCoach]

Steve makes a great point about avoiding reserved/keywords..

That is why in the tables I suggested the name were changed:

You would end up with this:

tblClientContacts

• ClientID
• ClientConactID (PK)
• ContactData (text)
• ContactTypeID (foreign key (FK) to lookup table)

tblLookupContactTypes

• ContactTypeID (PK)
• ContactTypeDesc (Work Phne, Work Fax, work email, home phone, home email, etc.)
• ContactTypeCategory (E = email, P = Phone, M = mobile)

[ThornofSouls]

Okay i have set up the tables and the relationships. I believe it is something like this.

One to many from tblClients to tblEmails & tblPhoneNumbers.
then one to many from tblLookupContactTypes to tblEmails & tblPhoneNumbers.
I will have a sample db uploaded to the weekend. (Just have to translate the titles.)

building a new from for adding clients new, thus i am not sure if i got this query right. Oh well if it don't work i just have to try another method.

[HiTechCoach]

Sounds like you are making great progress.

...
then one to many from tblLookupContactTypes to tblEmails & tblPhoneNumbers.

To me that is a HUGE RED FLAG normalization indicator that the tables tblEmails & tblPhoneNumbers should be combined. That is why I have been recommending it.

[ThornofSouls]

Here is a sample db on my project. Click me to Download.

I have struggled with this quite a bit now. i am not sure if i got it all right with the table structure and relations in my design, but as long something can be improved, there is more to learn.
Feel free to look around and test the db, the structure, code, relations and so on. When i worked with the form for new clients, i found it quite hard to find a good design and how i should structure the code, (I feel more secure with vba then macros, the vba feels easier to understand then the macros for me, thus the macros is supposed to be an easier tool)

Other then support for multiple contact details i have a few other projects i haven't solved yet. it is a work in progress and if you have any answers one them, i would like to hear.

[ssanfu]

I don't think the relationship between "tblAuthorization" and "tblUsers" should be 1-to-1. Delete the link and recreate it.

You have used a couple of reserved words: "Type" as field names in two tables and "Public" as a module name.

Not sure about these tables:
tblErrands - not sure about the fields.
tblErrandsAfter - can't tell what this tables is for
tblErrandsBefore- can't tell what this tables is for


Caption for the control "Kommandoknapp6" is misspelled. "Cancle" should be "Cancel".


You have displayed the autonumber fields on a lot of forms. Should read this about autonumbers:
Autonumbers--What they are NOT and What They Are
http://www.utteraccess.com/wiki/index.php/Autonumbers

[ThornofSouls]

I have the auto auto number field visible when i am testing things with the form. And i will look over the reserved words. Humm i thought i changed tblAuthorization & tblUsers to 1 to many. Oh well.
Thx for the misspell head up

To explain the Errands tables, i am not sure if i have the correct term for them but you can see them as a event of a case.
Like a client can be involved with one or more cases, and each cases can have multiple events (Errands as a call em for now)

Emm how to describe the before and after table.
First i am not sure if the before and after table should be related to the errand table or the case table. Because the one i am building the database for haven't answered that question just jet, and it debentures me a bit.
Anyway the Errand table is just the event. The defferense between the Errand, errandBefore and ErrandAfter tables are, tblErrand is the main event, that is the thing, Then tblErrandBefore will have information about the client before he/she recived an errand. and ErrandAfter will have information about the client when the case is closed.

I don't really know how to explain that well. But we can say, when a new client is registered in the system, a user have to enter some inforamtion aout what the client before the client was placed in a errand. when the errand/ cases is closed they will enter that information in the errand after.

Way i splitted it like this is because they want to know pretty much the same things but there is a difference in how much

[ThornofSouls]

Then if we have a table relationship as shown on the image. How can i design the form so it is able to take more then a single (Phonnumber, mobilenumer fax-number) input. When a user enters the data in a new record so the data is correctly saved to the related record. I assume each time a user enters a new number, that number has to be saved to a new record related to the user record.
Imge.

[ThornofSouls]

I got this working now and it works great. I made it quite simple and stuck with just some few tables, all i have is a user, addresses, emails, numbers and category table. The user table have a one to many relationship to the contact information tables and the category table have a one to many relationship to the contact information tables as well. The forms are continuous forms and the categories are determined with a query so the user can only pick a valid category for the number (phone number, mobile number, work number.) and so on.

This works really well for the needs i have right now. Then again if both the users and employees need multiple contact information details it feels like it would be an good ideé to link the user and employee table to a junction table before linking them with the contact information tables.

The table structure i have.

tblUsers

• UserID (PK - Autonumber)
• FirstName
• LastName
• DateofBrith

tblNumbers

• UserID (PK - Number)
• NumberID (PK - Autonumber)
• Number
• CategoryID (FK - Number)

tblAddresses

• UserID (PK - Number)
• AddressID (PK - Autonumber)
• Address
• City
• ZIP
• CategoryID (FK - Number)

tblEmails

• UserID (PK - Number)
• EmailID (PK - Autonumber)
• Email
• CategoryID (FK - Number)

tblCategory

• CategoryID (PK - Autonumber)
• Category

The code on the form for adding new users to the database.

Option Compare Database
Option Explicit


Private Sub btnCancel_Click()
    If Me.Dirty = True Then
        Me.Undo
        Me.Refresh
    End If
    DoCmd.Close acForm, Me.Name
    DoCmd.OpenForm "frmMenu", acNormal
End Sub


Private Sub btnSave_Click()
    Dim intStatus As Integer
            intStatus = saveCriterias()
End Sub


Private Function saveCriterias()
On Error GoTo Err_Handler


    Dim CritItems As New Collection
    Dim CritItem As Variant
    Dim critValidated As Boolean
    Dim ValidationCriteria As Integer
            ValidationCriteria = 0


    With CritItems '>>> 5 Items <<<'
        .Add Me.txtClientKey
        .Add Me.txtFirstName
        .Add Me.txtLastName
        .Add Me.txtDateOfBirth
        .Add Me.txtRegisterdOn
    End With
    For Each CritItem In CritItems
        If Not IsNull(CritItem) Then
            ValidationCriteria = ValidationCriteria + 1
            CritItem.BackColor = RGB(252, 230, 212)
        Else
            ValidationCriteria = ValidationCriteria - 1
            CritItem.BackColor = RGB(255, 80, 80)
        End If
            Debug.Print ValidationCriteria
            'MsgBox (ValidationCriteria)
        Next CritItem
        Select Case ValidationCriteria
            Case Is <= 4
                critValidated = False
                ValidationCriteria = -1
            Case 5
                critValidated = True
            Case Else
                critValidated = False
                strMsg = ("The form validation could not be confirmed.")
                intResponse = MsgBox(strMsg, vbOKOnly)
                GoTo Err_Handler
        End Select


        If critValidated = False Then
            MsgBox (ValidationCriteria)
            Me.Dirty = True
            strMsg = ("The form does not meet all the required criterias.")
            intResponse = MsgBox(strMsg, vbOKOnly)
            Exit Function
        Else
            MsgBox (ValidationCriteria)
            If ValidationCriteria = 5 Then
                If Me.Dirty = True Then
                    Me.Dirty = False
                    DoCmd.Close acForm, Me.Name
                    DoCmd.OpenForm "frmMenu", acNormal
                End If
            End If
        End If
Exit_Process:
    Me.Refresh
    Exit Function
Err_Handler:


    Resume Exit_Process
End Function


Private Sub btnUnDo_Click()
    Me.Undo
    Me.Refresh
End Sub

Thank you for your advises, instructions and help.

//ThornofSouls