Reporting via a secondary record to to primary record in table that has relationship to inself
Hello all,
I have created 1 table [PROJECTS] that maintains projects and I have three types of projects.
A master project exists of multiple sub projects so I have created a relationship from table [PROJECTS] to table [PROJECTS] and a {project type} field to control the behavior of fields. A single project is a project on itself.
Single & Master projects have a unique {project reference} field.
Single & Sub projects have a unique {center} field.
The Master abs Sub projects are connected via the {project reference1} field
Sub projects don't have a project reference although i created a {project reference sub} filed which concatenates the {project reference & center} to a unique value for all sub projects
There is a one-to-many relation to a child table [PRJBUDGET] and in this table i can add lines per item to create a total budget for a project and track status and type and provider and approvals and and etc etc
For single and sub type projects I have created several embedded reports that show the different budget records grouped by type and total summaries as liked and needed.
So far so good.
For the master projects I also would like to make some embedded report to show all the sub (child) budget records.
This could be done either showing all records for all locations or just summed per type per site which is i believe a secondary issue.
The first issue I struggle is how to pass the selection criteria between the Master project record and the report on several sub-projects.
Hope my explanation makes sense.
Thanks for any ideas and suggestions
below a printscreen of the internal relationship
------------------------------
Jan-Willem S
------------------------------
I have created 1 table [PROJECTS] that maintains projects and I have three types of projects.
- Single projects
- Master projects
- Sub projects
A master project exists of multiple sub projects so I have created a relationship from table [PROJECTS] to table [PROJECTS] and a {project type} field to control the behavior of fields. A single project is a project on itself.
Single & Master projects have a unique {project reference} field.
Single & Sub projects have a unique {center} field.
The Master abs Sub projects are connected via the {project reference1} field
Sub projects don't have a project reference although i created a {project reference sub} filed which concatenates the {project reference & center} to a unique value for all sub projects
There is a one-to-many relation to a child table [PRJBUDGET] and in this table i can add lines per item to create a total budget for a project and track status and type and provider and approvals and and etc etc
For single and sub type projects I have created several embedded reports that show the different budget records grouped by type and total summaries as liked and needed.
So far so good.
For the master projects I also would like to make some embedded report to show all the sub (child) budget records.
This could be done either showing all records for all locations or just summed per type per site which is i believe a secondary issue.
The first issue I struggle is how to pass the selection criteria between the Master project record and the report on several sub-projects.
Hope my explanation makes sense.
Thanks for any ideas and suggestions
below a printscreen of the internal relationship
------------------------------
Jan-Willem S
------------------------------