Problem linking two tables(relational)

[Hairy]

I am having a problem with linking two tables in order to make a relational database. My problem is that whenever I change an information in one table, it does not seem to change in the other.

Any solutions?

[June7]

Why would something need to change in 'other' table? Want to provide db for analysis? Follow instructions at bottom of my post.

[Hairy]

Why would something need to change in 'other' table? Want to provide db for analysis? Follow instructions at bottom of my post.

For example if i link two tables having one common field, telephone number, so if i change the telephone number in one table it does not automatically change in the other.

[June7]

Why is the telephone number in both tables? This is duplication of data.

[Hairy]

Why is the telephone number in both tables? This is duplication of data.

If you can see in the attachment( I hope that it got uploaded) it will be much easier to tell me what my problem is.

[June7]

You want phone number to be primary key? What if more than one patient (family members) have the same phone number?

Use form/subform arrangement to synchronize saving primary key as foreign key in child record. http://office.microsoft.com/en-us/ac...010098674.aspx

[Hairy]

I have read the guidance and found something new. My tables do not have the infinity sign. Can you please tell me how to make the infinity sign appear?

[June7]

Select 'Enforce referential integrity' in the Relationshp builder JoinType settings.

Do you really want the RegistrationNumber to be a text and not Autonumber?

Advise no spaces in names. Better would be CPRNUM or CPR_Num or CPR_Number.

[Hairy]

Select 'Enforce referential integrity' in the Relationshp builder JoinType settings.

Do you really want the RegistrationNumber to be a text and not Autonumber?

Advise no spaces in names. Better would be CPRNUM or CPR_Num or CPR_Number.

I have done what you told, and thanks for that. But i need to know how to automate the changes as so when i want to change the phone number of a person in the first table it will automatically change in the other.(eg if a person changed his/her number)

[smithse]

Hi hairy,

As June7 correctly makes note, there is duplication of not only the phone number, but also the patient name. IMO the patients name and phone number should be in one table, and only in that table.
You should have a unique number which identifies that person/patient which is what I think your 'CPR' number is. If that's all your registration table is storing, then your CPR number should also be in the patient table.
If the "sessions" table stores information about the visits for that patient, then there should be one record for each visit. This would have a One-to-Many relationship with the patient table on the CPR number. In that case, when you update the CPR number (though it seems one of those numbers that shouldn't ever need to be changed) as long as you have referential integrity enforced on the relationship, the change will occur in the "sessions" table.

[Hairy]

So what your saying is that in the other table, There shouldnt be patients name and phone number?
And which two fields do you think I should best relate?

[Hairy]

I just have one more problem if you may help me. How can I set a foreign key on the CPR field?

[smithse]

So what your saying is that in the other table, There shouldnt be patients name and phone number?
And which two fields do you think I should best relate?

Hi hairy,

So, above is the relationships that your application currently has.

Modify your patients table to include the name, phone number, gender, CPR number (PK), past history. IMO, your current complaint and treatment fields should be stored in a session table record, as these are likely to change with each visit? Just a thought.
Querying the patient's current complain is as simple as selecting the latest complaint from the session table for that patient.

Your "Session" table should contain a SessionID (PK), CPRNumber (which is linked using a One-to-Many relationship on the CPRNumber in the patients table), DateOfVisit and any other information that you wish to store. Once again, querying the number of sessions or visits by a patient is straighforward; simply select the count of session records for that particular patient.

I hope that helps.

Below is the relationship you should adopt. This prevents or should I say alleviates the neccessity to store the same data in more than one table:

[June7]

I agree with smithse. However, smithse, I can't see the images you tried to attach.

[smithse]

I agree with smithse. However, smithse, I can't see the images you tried to attach.

Damn, don't you hate that LOL.
Sorry about that, and here is the image (hopefully) of the relationship that you should probably use. I will skip adding the relationship that you had beforehand though.