Crosstab Issue

[gary.newport]

I have a crosstab query which, when runs, works perfectly.

The column heading is the first letter of a criteria type (P, M and D from PASS, MERIT and Distinction). The row headings are the variable criteria values (a value from 1 to x where x can be any value from 3 to 10). The value field is the description; which is text defining that particular criteria needs (so, you can use the crosstab to see what you need to do to achieve P1, P5, M1, M2, D3, etc).

I have included the linking field unit_id since this is used to limited the crosstab data to a specific unit within a course (43 units in total). The SQL is:

TRANSFORM First(tbl_criteria.criteria_description) AS FirstOfcriteria_description
SELECT tbl_criteria.criteria_no
FROM tbl_criteria
WHERE (((tbl_criteria.unit_id)=60))
GROUP BY tbl_criteria.criteria_no
ORDER BY tbl_criteria.criteria_no, Left([criteria_type],1) DESC 
PIVOT Left([criteria_type],1);

As I say, the crosstab itself works fine.

Yet, if I try to put this onto an existing form that has unit_id as a field I cannot link the two because the crosstab does not show the unit_id as an available field.

Currently I have restricted the unit_id to the value of 60 (happens to be unit 17) simply for testing but want this to be adjusted to link in with the unit selected on the main form.

Why won't it show the linking field?