Crosstab Query Column Question

[Guest]

Hello!

I am currently in the process of trying to work with a crosstab query in MS
Access. In the rows, I have a list of names, and in the columns, I have
dates. I would like the dates to be formatted as follows:

(First column for names)
Second column: Fiscal Year 2005 (July 2004 results - June 2005 results)
Third column: Fiscal Year 2006 (July 2005 results - June 2006 results)
Fourth Column: July 2006
Fifth Column: August 2006
Sixth Column: September 2006 ((and so on...))

Is there any way to do this? I would like to import this information into a
report.

Thank you!

 

[Guest]

Do believe so. Post your other field names and sample data.

 

[Guest]

Here is the SQL statement for it, if that helps:

TRANSFORM Sum(Query1.Hours) AS SumOfHours
SELECT Query1.FundNum, Query1.ProjNum, Query1.ProjSub1, Query1.ProjSub2,
Query1.LastName, Sum(Query1.Hours) AS [Total Of Hours]
FROM Query1
GROUP BY Query1.FundNum, Query1.ProjNum, Query1.ProjSub1, Query1.ProjSub2,
Query1.LastName
PIVOT Format([Date],"yyyy");

Let me know if you need any additional information. Thanks!

 

[Guest]

I have to use another query before the crosstab to form the dates into FY or
months. I named it Leslie_W but you can change if you also edit it
in the crosstab.
Leslie_W ---
SELECT Sum(Query1.Hours) AS SumOfHours, Query1.FundNum, Query1.ProjNum,
Query1.ProjSub1, Query1.ProjSub2, Query1.LastName,
IIf(Format(DateAdd("m",18,[LaborDate]),"yyyy")<Format(Date(),"yyyy"),"FY " &
Format(DateAdd("m",18,[LaborDate]),"yyyy"),Format([LaborDate],"mmmm yyyy"))
AS [Fiscal Year]
FROM Query1
GROUP BY Query1.FundNum, Query1.ProjNum, Query1.ProjSub1, Query1.ProjSub2,
Query1.LastName,
IIf(Format(DateAdd("m",18,[LaborDate]),"yyyy")<Format(Date(),"yyyy"),"FY " &
Format(DateAdd("m",18,[LaborDate]),"yyyy"),Format([LaborDate],"mmmm yyyy"))
ORDER BY
IIf(Format(DateAdd("m",18,[LaborDate]),"yyyy")<Format(Date(),"yyyy"),"FY " &
Format(DateAdd("m",18,[LaborDate]),"yyyy"),Format([LaborDate],"mmmm yyyy"));


TRANSFORM Sum(Leslie_W.SumOfHours) AS Hours
SELECT Leslie_W.FundNum, Leslie_W.ProjNum, Leslie_W.ProjSub1,
Leslie_W.ProjSub2, Leslie_W.LastName, Sum(Leslie_W.SumOfHours) AS [Total Of
Hours]
FROM Leslie_W
GROUP BY Leslie_W.FundNum, Leslie_W.ProjNum, Leslie_W.ProjSub1,
Leslie_W.ProjSub2, Leslie_W.LastName
PIVOT [Fiscal Year] IN("FY 2005","FY 2006","July 2006","August 2006",
"September 2006", "October 2006", "November 2006", "December 2006");

You will need to finis editing the IN statement to fill the rest of the
months.

 

[Guest]

Karl,

Thank you for your help. How would I modify the [LaborDate] to pull down
the exact dates I need? For example, our Fiscal Year runs from July 1 to
June 30?

Thanks again!
Leslie

I have to use another query before the crosstab to form the dates into FY or
months. I named it Leslie_W but you can change if you also edit it
in the crosstab.
Leslie_W ---
SELECT Sum(Query1.Hours) AS SumOfHours, Query1.FundNum, Query1.ProjNum,
Query1.ProjSub1, Query1.ProjSub2, Query1.LastName,
IIf(Format(DateAdd("m",18,[LaborDate]),"yyyy")<Format(Date(),"yyyy"),"FY " &
Format(DateAdd("m",18,[LaborDate]),"yyyy"),Format([LaborDate],"mmmm yyyy"))
AS [Fiscal Year]
FROM Query1
GROUP BY Query1.FundNum, Query1.ProjNum, Query1.ProjSub1, Query1.ProjSub2,
Query1.LastName,
IIf(Format(DateAdd("m",18,[LaborDate]),"yyyy")<Format(Date(),"yyyy"),"FY " &
Format(DateAdd("m",18,[LaborDate]),"yyyy"),Format([LaborDate],"mmmm yyyy"))
ORDER BY
IIf(Format(DateAdd("m",18,[LaborDate]),"yyyy")<Format(Date(),"yyyy"),"FY " &
Format(DateAdd("m",18,[LaborDate]),"yyyy"),Format([LaborDate],"mmmm yyyy"));


TRANSFORM Sum(Leslie_W.SumOfHours) AS Hours
SELECT Leslie_W.FundNum, Leslie_W.ProjNum, Leslie_W.ProjSub1,
Leslie_W.ProjSub2, Leslie_W.LastName, Sum(Leslie_W.SumOfHours) AS [Total Of
Hours]
FROM Leslie_W
GROUP BY Leslie_W.FundNum, Leslie_W.ProjNum, Leslie_W.ProjSub1,
Leslie_W.ProjSub2, Leslie_W.LastName
PIVOT [Fiscal Year] IN("FY 2005","FY 2006","July 2006","August 2006",
"September 2006", "October 2006", "November 2006", "December 2006");

You will need to finis editing the IN statement to fill the rest of the
months.
--
KARL DEWEY
Build a little - Test a little

Here is the SQL statement for it, if that helps:

TRANSFORM Sum(Query1.Hours) AS SumOfHours
SELECT Query1.FundNum, Query1.ProjNum, Query1.ProjSub1, Query1.ProjSub2,
Query1.LastName, Sum(Query1.Hours) AS [Total Of Hours]
FROM Query1
GROUP BY Query1.FundNum, Query1.ProjNum, Query1.ProjSub1, Query1.ProjSub2,
Query1.LastName
PIVOT Format([Date],"yyyy");

Let me know if you need any additional information. Thanks!