Passing Primary and Foreign Key values through linked tables

[Pedrowedro]

Hi,
Please can some one help with this. I am trying to get my head round cascading foreign and primary key data values.


I have three tables:

-tblContract
ContractID PK (autonumber)
BoNumber


-tblKnowledgeReport
KnowledgeID PK (autonumber)
ContractID FK
TypeOfReport

-tblMetricsReport
MetricsID
KnowledgeID FK
QuestionText
AnswerText

I am trying to append data to the tables above from the data in the following table:

-tblProjectParticulars
MetricsID
BoNumber
ReportType
QuestionText
Answer

If a BoNumber exists in tblContract then the ContractID is to be used to create an entry in the KnowledgeReport table ,triggering the autonumber of the KnowledgeID, which in turn passes this through to the tblMetrics.
If a BoNumber does exist then a new entry in tblContract is created with the new ContractID autonumber being used further in the branch.

Your help is very much appreciated
Pete

 

[Remou]

Do you mean ...

1. Update tblContract from tblProjectParticulars where BoNumber is not null;
2. Update tblKnowledgeReport from tblProjectParticulars inner joined to tblContract on BoNumber;
3. Update tblMetricsReport from tblProjectParticulars inner joined to tblContract on BoNumber inner joined to tblKnowledgeReport on ContractID?

I think that you will need to add BoNumbers where they do not exist, or else add another field that can be used for matching.

 

[Pedrowedro]

Thanks for your feed back Remou,

The structure is tblContract inner joined ContractID to table tblKnowledgeReport which in turn is inner joined by field KnowledgeID to tblKnowledgeReport.

What I have done is set up some none duplicate indexing, and created multiple update queries from tblProjectParticulars for each of the other tables. Amazingly this has achieved what I was after.

Thanks again for you swift reply!