Report from multiple tables (Parent, Child)

[thejkfarr]

My clients have requested that I combine 3 reports into one.

Table 1 (Parent) Jobs (Job ID, Customer)

Table 2 (Child) TimeLines (fkJobID, Date, Employee, Start, End)

Table 3 (Child) EquipmentLines (fkJobID, EQID, Date, Start, End)

Table 4 (Child) NotesLines (fkJobID, NoteID, Date, Start, End)

Currently I report on each Child table.

My clients want the report to look like:

JOBID (from Table1)

Date, Employee, Start, End (from Table2)

Date, EQID, Start, End (from Table3)

Date, NoteID, Start, End (from Table4)

Should I combine Table2-4 into one large table? (would be a major rewrite), loop script to copy related records to a reports table? Or is there a better way?

What direction would you take?

[comment]

How many of each does a typical job have, and how many at most?

[thejkfarr]

1 to 10 possible pages of data Total, up to 5 pages for Table 2, 3 for Table 3, 1 for Table 4)

[comment]

Ahm... that's not what I asked. The question is how many TimeLines records does one job have - typically and at most. The same about EquipmentLines and NotesLines.

[thejkfarr]

Timelines typically has 20-140 records, EquipmentLines has 10-40, Notes has around 20.

[comment]

That's sort of borderline. I wanted to see if printing portals was a feasible option. Perhaps you could do it by printing from the Timelines table, with portals to the other 2 tables in the sub-summary part.

I would prefer to import all the child records into a reporting table.

[thejkfarr]

Then that's the route i'll take.

Thanks Comment!