Create column headings in crosstab

[Guest]

I have a crosstab query I want to create a report from. I understand I need
to create column headings which would be Investigator, combinedtotal, Total
Of case_num, and 4 or 5 columns for week ending dates depending on how many
are in the month which come from the fiscal_week_end_date field. I've added
the SQL below; can anybody give me an example of how to add these? I've
looked in Help but could use some more help! Thanks.

PARAMETERS [Beg Week Date] DateTime, [End Week Date] DateTime;
TRANSFORM Count([qry_Apprehensions by Investigator].case_num) AS
CountOfcase_num
SELECT [qry_Apprehensions by Investigator].investigator, [qry_Apprehensions
by Investigator].case_number, [qry_Apprehensions by
Investigator].combinedtotal, Count([qry_Apprehensions by
Investigator].case_num) AS [Total Of case_num]
FROM [qry_Apprehensions by Investigator]
WHERE (((Format([fiscal_week_end_date],"Short Date"))>=[Beg Week Date] And
(Format([fiscal_week_end_date],"Short Date"))<=[End Week Date]))
GROUP BY [qry_Apprehensions by Investigator].investigator,
[qry_Apprehensions by Investigator].case_number, [qry_Apprehensions by
Investigator].combinedtotal
PIVOT Format([fiscal_week_end_date],"Short Date");

 

[David Lloyd]

One approach is to create a dynamic crosstab report where the column
headings are assigned at runtime based on the results of the crosstab query.
The following KB article provides information on doing this:

http://support.microsoft.com/default.aspx?scid=kb;en-us;328320

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


I have a crosstab query I want to create a report from. I understand I need
to create column headings which would be Investigator, combinedtotal, Total
Of case_num, and 4 or 5 columns for week ending dates depending on how many
are in the month which come from the fiscal_week_end_date field. I've added
the SQL below; can anybody give me an example of how to add these? I've
looked in Help but could use some more help! Thanks.

PARAMETERS [Beg Week Date] DateTime, [End Week Date] DateTime;
TRANSFORM Count([qry_Apprehensions by Investigator].case_num) AS
CountOfcase_num
SELECT [qry_Apprehensions by Investigator].investigator, [qry_Apprehensions
by Investigator].case_number, [qry_Apprehensions by
Investigator].combinedtotal, Count([qry_Apprehensions by
Investigator].case_num) AS [Total Of case_num]
FROM [qry_Apprehensions by Investigator]
WHERE (((Format([fiscal_week_end_date],"Short Date"))>=[Beg Week Date] And
(Format([fiscal_week_end_date],"Short Date"))<=[End Week Date]))
GROUP BY [qry_Apprehensions by Investigator].investigator,
[qry_Apprehensions by Investigator].case_number, [qry_Apprehensions by
Investigator].combinedtotal
PIVOT Format([fiscal_week_end_date],"Short Date");