Form and Sub-Form Problem

[magister011]

I am using Access 2003 to try to make a small database example to play with for a later bigger project. Here is how the database is set up:

tblProject
-fldProjPK
-fldProjNum
-fldProjNam
-fldProjContNum

tblIntermediate
-fldIntPK
-fldIntProjFK1
-fldIntProjFK2

tblProject_1
This is a virtual copy of tblProject

I have a relationship link from tblProject, fldProjPK to tblIntermediate, fldIntProjFK1 and from tblProject_1, fldProjPK to tblIntermediate, fldIntProjFK2 to make a query.

I am also showing in my query, fldProjNum from tblProject, fldProjNam from tblProject, fldProjContNum from tblProject, fldProjNum from tblProject_1, and fldProjNam from tblProject_1.

My subform is a continuous form with the record source coming from the query.

My main form is a single form with the record source coming from my project table.

What I am trying to do is to get my data to show on the main form with the Project Number, Project Name and the Contract Number. We have several instances where we have the same Project Number as a Contract Number but in the Sub Form we may have different Project Numbers and Project Names that are part of that main Project Name and Number.

We also may have on the occasion where we do not have a main Project Number but a main Project Name along with different Project Numbers and Names that I want shown in the sub form.

I am going to try to enclose a copy of the database so that all of this will make sense.

Please feel free to ask me any questions. This is a major stepping stone for the much bigger database that I am working on.

Thank you so much for anyones assistance.
Dave (aka magister011)

 

[Fifty2One]

To place a subform you need to be able to establish a Parent Child relationship so access knows what to populate into the subform. In the design view of the main form you can just drag and drop the subform and then go into properties and assign the parent child relationship.

 

[magister011]

I did try that, but I want to use the surrogate keys in my intermediate table. That property would not reference that table. It would only refer to the two project tables. If there is another way to make this relationship, I am open to suggestions.

Thanks
Dave

 

[gemma-the-husky]

tblProject
-fldProjPK
-fldProjNum
-fldProjNam
-fldProjContNum

tblIntermediate
-fldIntPK
-fldIntProjFK1
-fldIntProjFK2

tblProject_1
This is a virtual copy of tblProject

lets go back to the start

what are you trying to model - what is a project, and what is an intermediate project. how do they relate? this is where you need to start - then everything will be easy.

basically you have to structure your data as 1-many relationships only.

so 1 project has several intermediate projects. - and the primary key of the project becomes a reference item in the intermediate table - if its not like this it wont work - and if this doesnt reflect your real world situation, the data structure needs redesigning. Having two FKS in the intermdiate table looks suspect immediately. Are you saying an intermediate table can realte to multiple projects - if so your structure is wrong - ie not normalised

then, why do you need a copy of the project table? what are you trying to do with this?