Cross ref to a sub-report

[Martin Hopkins]

Hello,

In a report I run, the details of my personnel, who should hold a certain
qualification are displayed. In the sub-report included in the report ,
lists all personnel who hold the qualification whether they need to or not.
This way I can identify the level of training required.

What I would like to do, is have the persons post title highlighted RED if
the require the qualification BUT do not appear in the sub report. This way
I can identify future training needs.

The common field in both reports is the qualification title.

Any help greatly appreciated.

Martin Hopkins

 

[Allen Browne]

You cannot refer to the subreport data directly, but you can get that same
data into the main report's RecordSource so you can use it.

The main report is probably based on a query, where you have fields for the
person and the needed qualification. You could add a subquery to indicate
whether the person has the qualification or not.

Assuming that the main query is based on MainTable which has fields for
PersonID and QualificationID, and that the actual qualifications are in a
table called PersonQualification, you would create the subquery by typing
something like this into a fresh column of the Field row in query design:
HasQualification: EXISTS (SELECT ID FROM PersonQualification
WHERE PersonQualification.PersonID = MainTable.PersonID
AND Qualification.QualificationID = MainTable.QualificationID)
Note that the PersonQualification table does not need to be in the main
query.

Once you have that working, you can use Conditional Formatting (on the
Format menu in report design view) to color the text boxes.

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066