Cross Tab Query with Different Column Headings

[Guest]

I am trying to create a report that will allow a cross tab query to display
different column headings depending on a parameter chosen by the user example
below:

Data Sample:
Site Leader Manager Question1 Question2
Leader 1 Manager 1 70% 92%
Leader 1 Manager 2 40% 86%
Leader 1 Manager 3 99% 45%
Leader 2 Manager 1 70% 23%
Leader 2 Manager 2 40% 23%
Leader 2 Manager 3 99% 42%

Sooo Leader 1 wants to see his results I have a dropdown where they would
choose their name from a form the cross tab query looks at that field to
determine what data to show. I want the report to be as follows so we can
compare the results for each question by manager.

Manager1 Manager2 Manager 3
Question 1 70% 40% 99%
Question 2 92% 86% 45%


Hope this makes sense.

Thanks for any help you can provide

 

[Michel Walsh]

Since a field NAME is NOT easily accessible data, you may have to supply
that name as 'data' :


SELECT SiteLeader, Manager, Question1 As Question, 1 as qNumber FROM table
UNION ALL
SELECT SiteLeader, Manager, Question2 , 2 FROM table




Save that query under the name of qu1, as example. Then:


TRANSFORM question
SELECT qNumber
FROM qu1
WHERE SiteLeader=FORMS!FormName!ComboBoxName
GROUP BY qNumber
PIVOT manager



should do.



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Michel,

Thank you for your help.. I haven't used SQL very much .. hardly at all
actually.. there are 50 questions soo I started like this to see if it would
work for the first two.. but its not working can you tell me what im doing
wrong.

SELECT [QRY_Sales Conversion (Lead to FLM) 1].[Site Lead], [QRY_Sales
Conversion (Lead to FLM) 1].[FMR], [QRY_Sales Conversion (Lead to FLM)
1].[Sumof1c] As Question, 1 as qNumber
UNION ALL
SELECT [QRY_Sales Conversion (Lead to FLM) 1].[Site Lead], [QRY_Sales
Conversion (Lead to FLM) 1].[FMR], [QRY_Sales Conversion (Lead to FLM)
1].[Sumof1a] As Question, 2 as qNumber
UNION ALL

 

[John Spencer]

UNION ALL goes BETWEEN two queries and you need to identify the source
of the records in each query. So try

SELECT [QRY_Sales Conversion (Lead to FLM) 1].[Site Lead], [QRY_Sales
Conversion (Lead to FLM) 1].[FMR], [QRY_Sales Conversion (Lead to FLM)
1].[Sumof1c] As Question, 1 as qNumber
FROM [QRY_Sales Conversion (Lead to FLM) 1]
UNION ALL
SELECT [QRY_Sales Conversion (Lead to FLM) 1].[Site Lead], [QRY_Sales
Conversion (Lead to FLM) 1].[FMR], [QRY_Sales Conversion (Lead to FLM)
1].[Sumof1a] As Question, 2 as qNumber
FROM [QRY_Sales Conversion (Lead to FLM) 1]

While this may work for you, I very much doubt that you will be able to
UNION all 50 questions. You may have to build a table with the correct
structure and APPEND the records from the source table.

Something like
TableQuestions
-- SiteLead
-- FMR
-- QuestionNumber
-- QuestionResponse

Insert into TableQuestions (SiteLead, FMR, QuestionNumber, QuestionResponse)
SELECT [QRY_Sales Conversion (Lead to FLM) 1].[Site Lead]
, [QRY_Sales Conversion (Lead to FLM) 1].[FMR]
, [QRY_Sales Conversion (Lead to FLM) 1].[Sumof1c] As Question
, 1 as qNumber
FROM [QRY_Sales Conversion (Lead to FLM) 1]

And repeat that query for each of the 50 questions.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Michel,

Thank you for your help.. I haven't used SQL very much .. hardly at all
actually.. there are 50 questions soo I started like this to see if it would
work for the first two.. but its not working can you tell me what im doing
wrong.

SELECT [QRY_Sales Conversion (Lead to FLM) 1].[Site Lead], [QRY_Sales
Conversion (Lead to FLM) 1].[FMR], [QRY_Sales Conversion (Lead to FLM)
1].[Sumof1c] As Question, 1 as qNumber
UNION ALL
SELECT [QRY_Sales Conversion (Lead to FLM) 1].[Site Lead], [QRY_Sales
Conversion (Lead to FLM) 1].[FMR], [QRY_Sales Conversion (Lead to FLM)
1].[Sumof1a] As Question, 2 as qNumber
UNION ALL

Since a field NAME is NOT easily accessible data, you may have to supply
that name as 'data' :


SELECT SiteLeader, Manager, Question1 As Question, 1 as qNumber FROM table
UNION ALL
SELECT SiteLeader, Manager, Question2 , 2 FROM table




Save that query under the name of qu1, as example. Then:


TRANSFORM question
SELECT qNumber
FROM qu1
WHERE SiteLeader=FORMS!FormName!ComboBoxName
GROUP BY qNumber
PIVOT manager



should do.



Hoping it may help,
Vanderghast, Access MVP