SQL Crosstab Question

[Guest]

I have written the following SQL Statement:

TRANSFORM Sum(qryTSExportFY.Hours) AS SumOfHours
SELECT qryTSExportFY.ProjNum, qryTSExportFY.ProjSub1,
qryTSExportFY.ProjSub2, qryTSExportFY.LastName, Sum(qryTSExportFY.Hours) AS
TotalHours
FROM qryTSExportFY
GROUP BY qryTSExportFY.ProjNum, qryTSExportFY.ProjSub1,
qryTSExportFY.ProjSub2, qryTSExportFY.LastName
PIVOT qryTSExportFY.FiscalYear In ("FY05", "FY06", "July 2006", "August
2006", "September 2006", "October 2006", "November 2006", "December 2006",
"January 2007", "February 2007", "March 2007", "April 2007", "May 2007",
"June 2007");

My problem is that Access will create all of the columns, however, they are
not populated with the data from the pivot table. A SumofHours Column was
created, but the hours do not distribute. Any ideas where I went wrong?

Thanks for your help!

 

[Guest]

In a crosstab query, you only get one set of aggregations, that is on the
field you select to use as a Value, and in your case shows up on the
TRANSFORM line of the query.

1. Delete the "Sum(qry_TSExportFY.Hours) AS TotalHours " portion of the
SELECT clause

2. Do you really have all of the values "FY05", "FY06", "July 2006", ...
in your [FiscalYear] field? I would think you would have a column for
FiscalYear (FY05, FY06, FY07, FY08) and another column for FiscalMonth.

HTH
Dale

 

[Guest]

Dale,

I wanted to reference FY05 and FY06 in total for a summary only. Your
comments put me on the right track, and I'm good to go now. Thanks for your
help!

Leslie