Combining Data from dissimilar tables

[Wendy Parry]

I am being very dense on solving this problem and I know that it can be
quite simple.

I have two tables tblAcadGrade and tblPastoralGrade. The first table
will have a series of records associated to each student (a record for
each subject a student studies). The second table only has one record
for each student. The fields are dissimilar to eachother in each of the
tables. (So I can't use a UNION) They are both child tables of a master
F_PUPILS.

I need to eventually combine the multiple records for each student into
a report (no problem with that), with the record from tblPastoralGrade
in the footer of the report. This will basically make up a complete
school report.

I can't think of a better data model to make this any easier, but
suggestions would be gladly accepted.

Wendy

 

[Guest]

Wendy:

If you base the report on the tblAcadGrade table and group it on StudentID
on whatever the foreign key is called, you could embed another report based
on the tblPastoralGrade table as a subreport in the StudentID group footer,
linking the parent and subreport on the SudentID fields.

I f you are printing reports for multiple students set the ForceNewPage
property of the group footer to After Section.

Ken Sheridan
Stafford, England

 

[Wendy Parry]

Ken,
Thanks for that suggestion, appears a sound solution. The only thing is
though, when I put the subreport into the group footer, it doesn't
actually display the field results when previewing. What am I doing wrong?

Wendy

 

[Guest]

Wendy:

I'm not sure what the problem is. The subreport control's LinkMasterFields
and LinkChildFields properties should be StudentID, or whatever the name of
field in the two tables is which identifies the student. The subreport
should then show the row from its underlying table for the current student in
the main parent report.

I assume that if you open the subreport's underlying report independently
from the database window it does show records.

Ken Sheridan
Stafford, England