UnCrosstab within Access

[pocquet]

Hello All,


I have a crosstab table within a database.

I have tried to uncross tab this with the below query:

SELECT qry_SurveyBaseCC.Year, dbo_tbl_DR_CostCentreInformation.CC_Report_Region, dbo_tbl_DR_CostCentreInformation_1.CC_Name AS [Region Name], "UsedBefore" As Answer, sum(qry_SurveyBaseCC.UsedBefore)
FROM qry_SurveyBaseCC
INNER JOIN (dbo_tbl_DR_CostCentreInformation INNER JOIN dbo_tbl_DR_CostCentreInformation AS dbo_tbl_DR_CostCentreInformation_1 ON dbo_tbl_DR_CostCentreInformation.CC_Report_Region = dbo_tbl_DR_CostCentreInformation_1.CC_Number) ON qry_SurveyBaseCC.CC = dbo_tbl_DR_CostCentreInformation.CC_Number
WHERE (((qry_SurveyBaseCC.Year)>=2011 And (qry_SurveyBaseCC.Year)<=2017))
GROUP BY qry_SurveyBaseCC.Year, dbo_tbl_DR_CostCentreInformation.CC_Report_Region, dbo_tbl_DR_CostCentreInformation_1.CC_Name, qry_SurveyBaseCC.Internet, qry_SurveyBaseCC.UsedBefore, qry_SurveyBaseCC.plan, qry_SurveyBaseCC.LocalReputation, qry_SurveyBaseCC.CloseToHome, qry_SurveyBaseCC.Advert, qry_SurveyBaseCC.RecFamily, qry_SurveyBaseCC.RecMinister, qry_SurveyBaseCC.RecDocNursHome
UNION ALL
SELECT qry_SurveyBaseCC.Year, dbo_tbl_DR_CostCentreInformation.CC_Report_Region, dbo_tbl_DR_CostCentreInformation_1.CC_Name AS [Region Name], "plan" As Answer,  sum(qry_SurveyBaseCC.plan)
FROM qry_SurveyBaseCC
INNER JOIN (dbo_tbl_DR_CostCentreInformation INNER JOIN dbo_tbl_DR_CostCentreInformation AS dbo_tbl_DR_CostCentreInformation_1 ON dbo_tbl_DR_CostCentreInformation.CC_Report_Region = dbo_tbl_DR_CostCentreInformation_1.CC_Number) ON qry_SurveyBaseCC.CC = dbo_tbl_DR_CostCentreInformation.CC_Number
WHERE (((qry_SurveyBaseCC.Year)>=2011 And (qry_SurveyBaseCC.Year)<=2017))
GROUP BY qry_SurveyBaseCC.Year, dbo_tbl_DR_CostCentreInformation.CC_Report_Region, dbo_tbl_DR_CostCentreInformation_1.CC_Name, qry_SurveyBaseCC.Internet, qry_SurveyBaseCC.UsedBefore, qry_SurveyBaseCC.plan, qry_SurveyBaseCC.LocalReputation, qry_SurveyBaseCC.CloseToHome, qry_SurveyBaseCC.Advert, qry_SurveyBaseCC.RecFamily, qry_SurveyBaseCC.RecMinister, qry_SurveyBaseCC.RecDocNursHome
UNION ALL
SELECT qry_SurveyBaseCC.Year, dbo_tbl_DR_CostCentreInformation.CC_Report_Region, dbo_tbl_DR_CostCentreInformation_1.CC_Name AS [Region Name], "LocalReputation" As Answer, sum(qry_SurveyBaseCC.LocalReputation)
FROM qry_SurveyBaseCC
INNER JOIN (dbo_tbl_DR_CostCentreInformation INNER JOIN dbo_tbl_DR_CostCentreInformation AS dbo_tbl_DR_CostCentreInformation_1 ON dbo_tbl_DR_CostCentreInformation.CC_Report_Region = dbo_tbl_DR_CostCentreInformation_1.CC_Number) ON qry_SurveyBaseCC.CC = dbo_tbl_DR_CostCentreInformation.CC_Number
WHERE (((qry_SurveyBaseCC.Year)>=2011 And (qry_SurveyBaseCC.Year)<=2017))
GROUP BY qry_SurveyBaseCC.Year, dbo_tbl_DR_CostCentreInformation.CC_Report_Region, dbo_tbl_DR_CostCentreInformation_1.CC_Name, qry_SurveyBaseCC.Internet, qry_SurveyBaseCC.UsedBefore, qry_SurveyBaseCC.plan, qry_SurveyBaseCC.LocalReputation, qry_SurveyBaseCC.CloseToHome, qry_SurveyBaseCC.Advert, qry_SurveyBaseCC.RecFamily, qry_SurveyBaseCC.RecMinister, qry_SurveyBaseCC.RecDocNursHome
UNION ALL
SELECT qry_SurveyBaseCC.Year, dbo_tbl_DR_CostCentreInformation.CC_Report_Region, dbo_tbl_DR_CostCentreInformation_1.CC_Name AS [Region Name], "CloseToHome" As Answer, Sum(qry_SurveyBaseCC.CloseToHome) 
FROM qry_SurveyBaseCC
INNER JOIN (dbo_tbl_DR_CostCentreInformation INNER JOIN dbo_tbl_DR_CostCentreInformation AS dbo_tbl_DR_CostCentreInformation_1 ON dbo_tbl_DR_CostCentreInformation.CC_Report_Region = dbo_tbl_DR_CostCentreInformation_1.CC_Number) ON qry_SurveyBaseCC.CC = dbo_tbl_DR_CostCentreInformation.CC_Number
WHERE (((qry_SurveyBaseCC.Year)>=2011 And (qry_SurveyBaseCC.Year)<=2017))
GROUP BY qry_SurveyBaseCC.Year, dbo_tbl_DR_CostCentreInformation.CC_Report_Region, dbo_tbl_DR_CostCentreInformation_1.CC_Name, qry_SurveyBaseCC.Internet, qry_SurveyBaseCC.UsedBefore, qry_SurveyBaseCC.plan, qry_SurveyBaseCC.LocalReputation, qry_SurveyBaseCC.CloseToHome, qry_SurveyBaseCC.Advert, qry_SurveyBaseCC.RecFamily, qry_SurveyBaseCC.RecMinister, qry_SurveyBaseCC.RecDocNursHome
UNION ALL
SELECT qry_SurveyBaseCC.Year, dbo_tbl_DR_CostCentreInformation.CC_Report_Region, dbo_tbl_DR_CostCentreInformation_1.CC_Name AS [Region Name], "Advert" As Answer, sum(qry_SurveyBaseCC.Advert) 
FROM qry_SurveyBaseCC
INNER JOIN (dbo_tbl_DR_CostCentreInformation INNER JOIN dbo_tbl_DR_CostCentreInformation AS dbo_tbl_DR_CostCentreInformation_1 ON dbo_tbl_DR_CostCentreInformation.CC_Report_Region = dbo_tbl_DR_CostCentreInformation_1.CC_Number) ON qry_SurveyBaseCC.CC = dbo_tbl_DR_CostCentreInformation.CC_Number
WHERE (((qry_SurveyBaseCC.Year)>=2011 And (qry_SurveyBaseCC.Year)<=2017))
GROUP BY qry_SurveyBaseCC.Year, dbo_tbl_DR_CostCentreInformation.CC_Report_Region, dbo_tbl_DR_CostCentreInformation_1.CC_Name, qry_SurveyBaseCC.Internet, qry_SurveyBaseCC.UsedBefore, qry_SurveyBaseCC.plan, qry_SurveyBaseCC.LocalReputation, qry_SurveyBaseCC.CloseToHome, qry_SurveyBaseCC.Advert, qry_SurveyBaseCC.RecFamily, qry_SurveyBaseCC.RecMinister, qry_SurveyBaseCC.RecDocNursHome
UNION ALL
SELECT qry_SurveyBaseCC.Year, dbo_tbl_DR_CostCentreInformation.CC_Report_Region, dbo_tbl_DR_CostCentreInformation_1.CC_Name AS [Region Name], "RecFamily" As Answer, sum(qry_SurveyBaseCC.RecFamily) 
FROM qry_SurveyBaseCC
INNER JOIN (dbo_tbl_DR_CostCentreInformation INNER JOIN dbo_tbl_DR_CostCentreInformation AS dbo_tbl_DR_CostCentreInformation_1 ON dbo_tbl_DR_CostCentreInformation.CC_Report_Region = dbo_tbl_DR_CostCentreInformation_1.CC_Number) ON qry_SurveyBaseCC.CC = dbo_tbl_DR_CostCentreInformation.CC_Number
WHERE (((qry_SurveyBaseCC.Year)>=2011 And (qry_SurveyBaseCC.Year)<=2017))
GROUP BY qry_SurveyBaseCC.Year, dbo_tbl_DR_CostCentreInformation.CC_Report_Region, dbo_tbl_DR_CostCentreInformation_1.CC_Name, qry_SurveyBaseCC.Internet, qry_SurveyBaseCC.UsedBefore, qry_SurveyBaseCC.plan, qry_SurveyBaseCC.LocalReputation, qry_SurveyBaseCC.CloseToHome, qry_SurveyBaseCC.Advert, qry_SurveyBaseCC.RecFamily, qry_SurveyBaseCC.RecMinister, qry_SurveyBaseCC.RecDocNursHome
UNION ALL
SELECT qry_SurveyBaseCC.Year, dbo_tbl_DR_CostCentreInformation.CC_Report_Region, dbo_tbl_DR_CostCentreInformation_1.CC_Name AS [Region Name], "Minister" As Answer, sum(qry_SurveyBaseCC.RecMinister) 
FROM qry_SurveyBaseCC
INNER JOIN (dbo_tbl_DR_CostCentreInformation INNER JOIN dbo_tbl_DR_CostCentreInformation AS dbo_tbl_DR_CostCentreInformation_1 ON dbo_tbl_DR_CostCentreInformation.CC_Report_Region = dbo_tbl_DR_CostCentreInformation_1.CC_Number) ON qry_SurveyBaseCC.CC = dbo_tbl_DR_CostCentreInformation.CC_Number
WHERE (((qry_SurveyBaseCC.Year)>=2011 And (qry_SurveyBaseCC.Year)<=2017))
GROUP BY qry_SurveyBaseCC.Year, dbo_tbl_DR_CostCentreInformation.CC_Report_Region, dbo_tbl_DR_CostCentreInformation_1.CC_Name, qry_SurveyBaseCC.Internet, qry_SurveyBaseCC.UsedBefore, qry_SurveyBaseCC.plan, qry_SurveyBaseCC.LocalReputation, qry_SurveyBaseCC.CloseToHome, qry_SurveyBaseCC.Advert, qry_SurveyBaseCC.RecFamily, qry_SurveyBaseCC.RecMinister, qry_SurveyBaseCC.RecDocNursHome
UNION ALL
SELECT 
qry_SurveyBaseCC.Year, dbo_tbl_DR_CostCentreInformation.CC_Report_Region, dbo_tbl_DR_CostCentreInformation_1.CC_Name AS [Region Name], "DrNurse" As Answer, sum(qry_SurveyBaseCC.RecDocNursHome) 
FROM qry_SurveyBaseCC
INNER JOIN (dbo_tbl_DR_CostCentreInformation INNER JOIN dbo_tbl_DR_CostCentreInformation AS dbo_tbl_DR_CostCentreInformation_1 ON dbo_tbl_DR_CostCentreInformation.CC_Report_Region = dbo_tbl_DR_CostCentreInformation_1.CC_Number) ON qry_SurveyBaseCC.CC = dbo_tbl_DR_CostCentreInformation.CC_Number
WHERE (((qry_SurveyBaseCC.Year)>=2011 And (qry_SurveyBaseCC.Year)<=2017))
GROUP BY qry_SurveyBaseCC.Year, dbo_tbl_DR_CostCentreInformation.CC_Report_Region, dbo_tbl_DR_CostCentreInformation_1.CC_Name, qry_SurveyBaseCC.Internet, qry_SurveyBaseCC.UsedBefore, qry_SurveyBaseCC.plan, qry_SurveyBaseCC.LocalReputation, qry_SurveyBaseCC.CloseToHome, qry_SurveyBaseCC.Advert, qry_SurveyBaseCC.RecFamily, qry_SurveyBaseCC.RecMinister, qry_SurveyBaseCC.RecDocNursHome;

This does uncrosstab the results (the results are recorded as a -1 an answer) but the results don't come back as expected.

Its almost as if the above query is excluding results.

If theres a better way of uncross tabbing a report in access feel free to share.

Thanks

JC

 

[ranman256]

use the Xtab as a maketable query.
then use a series of queries to unMake the table.
similar to the pseudoCode below:

for c = 2 to columns.count
   sColHdr = col(c).row(1).text
    sSql = "insert into table [col1],sColHdr, col(c)
    docmd.runSql sSql
next

 

[xenou]

I'd recommend you create a simplified version of what you want to do as a test case and post the sample data with an explanation of the result you are trying to achieve. Also, you should say what a cross tab table is and how you got it in the first place - sometimes there are solutions that involve redesigning your data to have a better relational design.

Note: Also you should explain what you mean when "results don't come back as expected". How are they not what was expected?

 

[ranman256]

make N queries each to pull data from 1 column of the N columns.
put all queries in a macro.
run.