Report to only pick up relevant worksheet subforms

[micromoth]

I have a Project table with an auto-number project ID used as PK in
relationships.
I have 16 worksheet tables which all have the project ID as a FK.
A project might use Worksheet 1,4 and 7 while another project might use
16 and 12.
How do I build a single project report that for an individual project
picks up on and adds in (subforms) Forms 1,4 and 7 but ignores the
rest.
Do I need a query? If so how would I go about it.
Do I need 16 worksheet queries or just one big project query?

 

[Allen Browne]

These tables need to redesigned so they are normalized.

Firstly, can we confirm we are talking about Access here?
Excel has worksheets. Access does not.

If you are using Excel, please post your question to an Excel newgroup. If
you are using Access, please confirm, and indicate if the columns in the
different tables are nearly all the same, or very different from each other.

 

[micromoth]

I am using Access. The 16 worksheet tables cover things like curtain
manufacture or headboard manufacture so the tables are not similar in
either size or content although all have a ID column and a project ID
column. We call each of the paper forms a worksheet in the office, so
sorry for the confusion, I have 16 tables. The data set has been fully
normalised manually and each table passed throught the Access analyser
as a check (althought I dont know how good this is).

 

[Allen Browne]

Okay, one alternative would be to create a report (bound to the Project
table), with 16 subreports - one for each related table. Where a related
table has no content for a project, nothing will print, so it should give
the right result.

If you want to keep the subreports together, each one will need to go into a
section of its own within the main report. That means you will enter the
ProjectID field on 8 different lines in the Sorting And Grouping box, each
one with a Group Header and a Group Footer. This gives you 17 sections in
the report (8 x Group Header + 8 x Group Footer + 1 Detail), so you can
Keep Together.

 

[micromoth]

Thanks Allen
I will go for the simple option for just now. One report with 16 sub
reports, should be able to handle it (famous last words)
Cheers Graham