Newbbie with an Access relationship proble

[GRIFFIJ]

I have three tables please help me link them. I know that I probably should use a subforrm but I am trying to learn about linking tables together please help.


Table 1

Client_Details

PK ClientIDNo
DOB
ClientSurname
ClientFirst
ClientTitle

Table 2

Client addresss_details

PK ClientAddressID
ClientAddressPropertyName
ClientAddressFlatNumber
ClientAddress
ClentAddressTown
ClientAddressCity
ClientAddressCounty
ClientAddressPostalCode



Table 3

Client_Contact_Details

PKClientContactID
ClientHomePhoneNo
ClientMobilePhone
ClientEmailAddress

 

[dhookom]

My guess is that a client can have 0 to several addresses and 0 to several contact details. If so, you need to add fields to Table 2 and Table 3 to store the value from Client_Details.ClientIDNo.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[GRIFFIJ]

Can I Ask what sort of feild I need to add to hold the value would it be possible for you to show me an example please?

I was thinking you need another table like the following to link them all together. Just not sure how

Table 4

Client_Info_Link

PKClientInfoID
PKClientAddressID
PKClientContactID
PKClientIDno

 

[willir]

GRIFFIJ

First, great job in identifying that the address and phone or email addresses are related as one-to-many.

Per Duane's response...

Client addresss_details
PK ClientAddressID
PKClientIDNo - foreign key to Client_Details
ClientAddressPropertyName
ClientAddressFlatNumber
ClientAddress
ClentAddressTown
ClientAddressCity
ClientAddressCounty
ClientAddressPostalCode

Client_Contact_Details
PKClientContactID

PKClientIDNo - foreign key to Client_Details
ClientHomePhoneNo
ClientMobilePhone
ClientEmailAddress

For further reading material...
Fundamentals of Relational Database Design by Paul Litwin
Download document
Read on-line (HTML)

Micro$oft's answer to design and relationships...
Where to find information about designing a database in Microsoft Access
283878 - Description of the database normalization basics
304467 - ACC2000 Defining Relationships Between Tables in a Microsoft Access Database

And a really good article on queries
Harnessing the Power of Updatable Queries

Richard

 

[GRIFFIJ]

Dear Sir thank you very much for your help it is very appreciated. I have done the following.

In the Client_Details table

PK ClientIDNo
DOB
ClientSurname
ClientFirst
ClientTitle

This table links with a 1-M relationship to

Client addresss_details
PK ClientAddressID
PKClientIDNo
ClientAddressPropertyName
ClientAddressFlatNumber
ClientAddress
ClentAddressTown
ClientAddressCity
ClientAddressCounty
ClientAddressPostalCode

It also links to the Client_Contact_Details with a 1-M Relationship.

PKClientContactID
PKClientIDNo
ClientHomePhoneNo
ClientMobilePhone
ClientEmailAddress


So I have two links coming from the ClientIDno to the two other tables

The problem is now The one table links to the other two and when I try and enter data I get the following Inser Sub data sheet with Master and Child.

I get the feeling the should be a link between the tables somewhere

Cheers Joel

 

[dhookom]

I would open all tables in design view and set their subdatasheet view to None.

For data entry and editing, I would use a main form for table 1 and subforms for table 2 and table 3.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[willir]

Joel -- Have you created your relationships using the Relationship tool?

From the menu, "Tools" -> "Relationships.

Add your three tables.
Click on [PK ClientIDNo] on the main table and drag it to the PKClientIDNo on the Address table. At the popup window, click on "Enforce referential integrity".

Repeat the excercise for the phone / email. Click on [PK ClientIDNo] on the main table and drag it to the PKClientIDNo on the Phone / email table. Again, at the popup windowm "Enforce referential integrity".

Now create your forms. By creating the relationships first before creating the main form / subform, Access will automatically join the form / subform using the LinkMaster and LinChild based on the PKClientIDNo field.

Richard

 

[GRIFFIJ]

Dear Richard

I have created the the forms like you said after the relationships.

Now how do I link both forms back to the first I want to put a buttons on each form so the user can click on any form they like with no particular order. Does this make sense.

So here is an example of what I am trying to achieve

A user comes along nd types in the client details. the the havve the choice of whether to enter contact details or Address details.

Then from what ever form they then go to the can link to any other form but thew are staying with the same record

Many thanks Joel

 

[dhookom]

As I stated earlier you should consider using you main table as the record source for your main form. Place subforms bound to your other tables on your main form. Make sure the Link Master/Child properties are set to the client ID number fields.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]