Link field not visible in crosstab subreport (1 Viewer)

Hi there,

I have created a crosstab query and corresponding subreport. Now crosstab queries are new to me.
I need to only display the records pertaining to the associated JobNo or LocationID on the main report but whatever I try, when I come to link the reports, the above fields are not visible so I am doing something wrong?

The SQL for the query is:

TRANSFORM Count(ItemsInKits.EquipmentID) AS CountOfEquipmentID

SELECT ItemsInKits.EquipDescription

FROM (ItemsInKits

INNER JOIN Location ON ItemsInKits.LocationID = Location.LocationID)

INNER JOIN Equipment ON (Location.LocationID = Equipment.LocationID)

AND (ItemsInKits.EquipmentID = Equipment.EquipmentID)

GROUP BY ItemsInKits.EquipDescription, Equipment.LocationID, Location.JobNo

PIVOT ItemsInKits.KitNumber;

Many thanks in advance

doesn't look like your are selecting them to view, only group by - ensure that 'show' is selected

Hi there, thanks for your reply.
Where is the 'show' option?

toward the bottom of the query window near where you enter the fieldnames

Hi again and thank you for your reply.

In the query design window?

I have:

Field:
Table:
Total:
Crosstab:
Sort:
Criteria:
or:

I've been looking into this all day and still nowhere with it.
The crosstab works exactly like I want but no matter what I seem to do I cannot see the JobNo or LocationID when I come to link the fields in the main report and subreport?
Strange?

ah - sorry forgot this was a crosstab

select 'Row Heading' on the crosstab row for the Equipment.LocationID and Location.JobNo columns

for future reference, the show appears in select queries

Sorry for the late reply!

Yes, that worked.

Thank you very much for your help!