Link field not visible in crosstab subreport

[Sam Summers]

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

 

[CJ_London]

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

 

[Sam Summers]

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

 

[CJ_London]

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

 

[Sam Summers]

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?

 

[CJ_London]

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

 

[Sam Summers]

Sorry for the late reply!

Yes, that worked.

Thank you very much for your help!