Problem with tables

A

ANSWER

I have database with 18 tables. Table (tblPersonal_Info) is main table
indirectly linked to 7 tablest by relation 1 to many.
This 7 tables have next primary keys:

1/ tblExperience > Pk > 1. Experience code (Autonumber)
2. JMBG (personal identification number) (text)

2/ tblPersonalContact > Pk > 1. NumberCode
2. JMBG

3/ tblEducation > Pk > 1. EducationCode
2. JMBG

4/ tblFamily > Pk > 1. FamilyCode
2. JMBG

5/ tblComputer > Pk > 1. Cognition_of_computerCode
2. JMBG

6/ tblForeignLanguage > Pk > 1. Cognition_of_Foreign_LanguageCode
2. JMBG

7/ tblDevelopment > Pk > 1. DevelopmentCode
2. JMBG

When I add new record into main form I have no problems. Now if I want to
change JMBG in existent record in main
form, I can do this but old JMBG stay in other 7 tables.
I want when I change JMBG in main form automaticly changing in 7 others
tables. Can I make some update query or something else.
If you know couple solution, please let me know so I could find out what is
the best for me.

Thanks a lot.
 
T

Troy

All of your subsequent tables should not have JMBG recorded in them. It is
redundant data if you store it twice anywhere. This is not normalized
structure.

If JMBG is originated in the table "tblExperience", then your subsequent
tables should have a FK to the PK of "tblExperience".

If it does not originate there, then ALL of your tables listed need to have
a FK to the originating table where JMBG is initially created or stored.

You'll also need to set up a relationship between the originating table and
each table.

Now when you want to know the JMBG for a given child record (child = any
table that has JMBG as a FK to the originating table), your query will need
to join on that PK and FK field and return the JMBG from the originating
table.

--
Troy

Troy Munford
Development Operations Manager
FMS, Inc.
www.fmsinc.com


I have database with 18 tables. Table (tblPersonal_Info) is main table
indirectly linked to 7 tablest by relation 1 to many.
This 7 tables have next primary keys:

1/ tblExperience > Pk > 1. Experience code (Autonumber)
2. JMBG (personal identification number) (text)

2/ tblPersonalContact > Pk > 1. NumberCode
2. JMBG

3/ tblEducation > Pk > 1. EducationCode
2. JMBG

4/ tblFamily > Pk > 1. FamilyCode
2. JMBG

5/ tblComputer > Pk > 1. Cognition_of_computerCode
2. JMBG

6/ tblForeignLanguage > Pk > 1. Cognition_of_Foreign_LanguageCode
2. JMBG

7/ tblDevelopment > Pk > 1. DevelopmentCode
2. JMBG

When I add new record into main form I have no problems. Now if I want to
change JMBG in existent record in main
form, I can do this but old JMBG stay in other 7 tables.
I want when I change JMBG in main form automaticly changing in 7 others
tables. Can I make some update query or something else.
If you know couple solution, please let me know so I could find out what is
the best for me.

Thanks a lot.
 
Top