Keep 5 similar tables updated

[joee]

I have 5 tables which comes from different departments in our company.
All of them have the same key "project no" but hold different information in other colums.
As a starting point I have secured that they all have same no. of records meaning all "project no" are in all tables but some table might not contain other information in that record than the project no.
I have put them into a quirie and on a form I am able to look at all data from all 5 tables.
I have linked them one to one and it works fine untill I add a record to one of the tables. Afterwards I am not able to write into fields from other tables because there is no automatic creation of the record in the other tables.
Please How would this be best/easiest to make for me. ?
The reason why I have to keep the 5 tables is that regular updates are avaiable form any of them.

Appricate any comments which can make me any progress.

 

[Guus2005]

Don't know where to start.

You are using a query to connect all five tables using their primary key "Project No". If the PK doesn't exist in one of the tables, the Project No is not shown.

If you use a Left Outer Join, empty lines are introduced for each missing ProjectNo in the Right table and so on. However if the leftmost table is the one who is missing the PK, again the PK is not shown.

You can solve this by adding a ProjectNo table containing all available "Project No" and use this table as a basis for your application. Right Outer Join All other tables with this one and again empty fields are shown when that tables misses the PK.

Am i making any sense?

Best is to normalise your database!

 

[joee]

Thanks for your answer. That works but can you tell me how I easily can add a new Project no in all tables. Once I import a table which have had a new project no added in the department where it comes from I have to be able to fill in informtation to the other tables from the common form.Before I can do that I ned 1: to find the project numbers which might have been added. 2: To have the new project numbers created in all tables.
Appreciate any assistance, thanks

 

[Guus2005]

if you normalise the database you don't have to create the same project no in five(!) different tables.

again my advice: normalise.

the answer: five sql insert statements.