Need to replicate data in 2 tables

[magister011]

I have a rather unique problem that I have not seen solved on a forum yet. I am using MS Access 2003. I have three tables.

tblProject
-idsProjPK = Primary Key
-chrProjNum = Project Number
-chrProjNam = Project Name
-chrProjContNum = Project Contract Number

tblIntermediate
-idsIntPK = Primary Key
-lngzIntProjFK1 = Foreign Key from tblProject


-lngzIntProjFK2 = Foreign Key from tblProject1

tblProject1
-idsProjPK = Primary Key
-chrProjNum = Project Number
-chrProjNam = Project Name
-chrProjContNum = Project Contract Number

My relationships are:
one to many from tblProject.idsProjPK to tblIntermediate.lngzIntProjFK1 and a
one to many from tblProject1.idsProjPK to tblIntermediate.lngzIntProjFK2

The Master Field is tblProject.ProjPK
The Child Field is tblIntermediate.IntProjFK1

I have a form and a subform for the user to input the data. The subform contains the fields from tblProject1
-idsProjPK = Primary Key
-chrProjNum = Project Number
-chrProjNam = Project Name

and the fields from tblIntermediate
-lngzIntProjFK1 = Foreign Key from tblProject
-lngzIntProjFK2 = Foreign Key from tblProject1

The form contains the subform and the fields from tblProject
-idsProjPK = Primary Key
-chrProjNum = Project Number
-chrProjNam = Project Name
-chrProjContNum = Project Contract Number

Right now when the user enters data, the data goes into the correct tables. What I want to do is to be able to add data to the tblIntermediate but in reverse. I want to be able to enter data on the form and sub form and be able to have the data inputted to the tbl.Project and tblProject1 with both the

Master Field = tblProject.ProjPK
Child Field = tblIntermediate.IntProjFK1

and

Master Field = tblProject1.ProjPK
Child Field = tblIntermediate.IntProjFK2

I hope that I am explaining this correctly. I have been working on this for about a month with no luck at all in doing this both ways, unless I actually enter the data into the tblIntermediate, which is what I do not want to do. I also would not like to have to enter any of the data twice.

If anyone has any suggestions, I would greatly apprciate it.

Thank you very much for your time,
magister011

[dcrake]

Why have you got 2 identical tables this is a big sign of denormalised data structures.

David

[magister011]

I need the data to be in both places. The example that I will try to explain is like this. I have a road construction project with one contract number. Under this contract number this project has a main project name and main project number. The project may also have many "sub-projects" under this main project title. What I am trying to do is to type the information of the main project name, number and contract number on the form and the "sub-projects" on the subform. When I search at a later time if I want to only find a "sub-project", it will bring up what the main project it was under. I hope that this explanation makes sense.

Thanks,
magister011
aka Dave

[dcrake]

If you imagine your main project is the parent and the sub projects are the children you can ask either of them many questions.

To the Parent

Have you any children?
Can I see you children?

To the Child

Who are your parents?

A parent can have no children but a child cannot be an orphan, therefore if you know who is the child they should be able to tell you who their parents are.

Parents:
ParentId: PK - Autonumber
ParentName:
ParentFields:

Children:
ChildId: PK =Autonumber
ParentID: FK - PK Parent
ChildFields:

Again if your children had there own children then your relationship would travel down the ancestral route accordingly. As long as you can refer upwards you will not need duplication.

David

[magister011]

OK, I sort of understand that. Now to throw a bit of a "monkey wrench" into this design. I also have some projects that have the same contract number, a main project name but no main project number. They have "sub project" names and "sub project" numbers. This is for a county government and is set up for the accountants. We are trying to set up a system to track road construction materials. We track these materials by projects. This is why this part of the design is important to us.

Dave

[dcrake]

How can you have a contract number a project name but no project number?

When you set up your system you will hopefully have a table that contains projects and each project is given a name, admittedly it may have a name that is similar to another one but however the project Id will provide its uniqueness.

Then in your system if you wanted to assign a contract Id to a project you would simply pick a project from the list. Likewise contractors may work on various projects and as such you may say Ok you have been given the task of mainitaining roads A, B & C but roads A, B & C will be Projects A, B & C. It seems to me you need to be looking at referential integrity and data normalisation.

David