Go Back   Access World Forums > Microsoft Access Discussion > Reports

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 02-02-2016, 04:34 PM   #1
Sam Summers
Newly Registered User
 
Join Date: Sep 2001
Location: Inverness, Inverness-Shire, Scotland
Posts: 612
Thanks: 65
Thanked 2 Times in 2 Posts
Sam Summers is an unknown quantity at this point
Link field not visible in crosstab subreport

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

Sam Summers is offline   Reply With Quote
Old 02-02-2016, 05:04 PM   #2
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,390
Thanks: 40
Thanked 3,687 Times in 3,551 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Link field not visible in crosstab subreport

doesn't look like your are selecting them to view, only group by - ensure that 'show' is selected
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 02-03-2016, 07:46 AM   #3
Sam Summers
Newly Registered User
 
Join Date: Sep 2001
Location: Inverness, Inverness-Shire, Scotland
Posts: 612
Thanks: 65
Thanked 2 Times in 2 Posts
Sam Summers is an unknown quantity at this point
Re: Link field not visible in crosstab subreport

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

Sam Summers is offline   Reply With Quote
Old 02-03-2016, 02:41 PM   #4
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,390
Thanks: 40
Thanked 3,687 Times in 3,551 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Link field not visible in crosstab subreport

toward the bottom of the query window near where you enter the fieldnames
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 02-03-2016, 03:15 PM   #5
Sam Summers
Newly Registered User
 
Join Date: Sep 2001
Location: Inverness, Inverness-Shire, Scotland
Posts: 612
Thanks: 65
Thanked 2 Times in 2 Posts
Sam Summers is an unknown quantity at this point
Re: Link field not visible in crosstab subreport

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?
Sam Summers is offline   Reply With Quote
Old 02-03-2016, 04:30 PM   #6
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,390
Thanks: 40
Thanked 3,687 Times in 3,551 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Link field not visible in crosstab subreport

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
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
The Following User Says Thank You to CJ_London For This Useful Post:
Sam Summers (03-03-2016)
Old 03-03-2016, 06:20 AM   #7
Sam Summers
Newly Registered User
 
Join Date: Sep 2001
Location: Inverness, Inverness-Shire, Scotland
Posts: 612
Thanks: 65
Thanked 2 Times in 2 Posts
Sam Summers is an unknown quantity at this point
Re: Link field not visible in crosstab subreport

Sorry for the late reply!

Yes, that worked.

Thank you very much for your help!


Sam Summers is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Shrink detail section when subreport not visible EndofTheLine Reports 2 08-20-2009 09:29 AM
Link master field, on another subreport Gkirkup Reports 1 04-03-2009 01:15 AM
Data not visible in subreport when open from parent report MatMac Reports 0 04-02-2009 05:48 AM
Subreport is not visible if detail section is empty YZF Reports 5 01-03-2008 05:59 AM
Conditional visible label on a subreport not working. BarryMK Reports 11 09-29-2004 02:57 AM




All times are GMT -8. The time now is 04:32 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World