Grouping a Dynamic Crosstab Report

[Stephen Aker]

I'm adapting the code from Knowledge Base Article 328320, "How to create a
dynamic crosstab report in Access 2002" for use as a professional time
reporting system, hours by client and activity. It works for one week but I
can't figure out how to report up to 4 weeks at a time. The dynaset is set
up from a crosstab query with parameters, beginning and ending date, so the
dates are column headings with the times in the rows under the dates. I'd
like to set a group up on date, one per week, so each page is a different
page and group, but I've not been able to figure out how to do it. Anyone
familiar with working with reports from crosstab queries? Any help would be
appreciated.

 

[Duane Hookom]

This "canned" response uses months as the column heading date interval. You
should be able to modify it for dates rather than months. If you have
questions, come on back:

Try not to use "absolute" column headings for dates. You could possibly use
"relative" dates. For instance:
Form: frmA
Text Box: txtEndDate
Table: tblSales
Field: SaleDate
You want to show 12 months of sales in columns of a crosstab report.
Set the with menuing: Query|Parameter
Forms!frmA!txtEndDate Date/Time

Use this expression for your Column Headings:
ColHead:"Mth" & DateDiff("m",[SaleDate],Forms!frmA!txtEndDate)

This will produce Mth0, Mth1, Mth2, Mth3,... where Mth0 is sales from the
same month as the ending date on your form. Mth1 is the previous month etc.

Set your queries Column Headings property to:
"Mth0", "Mth1", "Mth2", "Mth3",.., "Mth11"
Build your report based on these "relative" months. If you need column
labels in your report, use text boxes with control sources of:
=DateAdd("m",0,Forms!frmA!txtEndDate)
=DateAdd("m",-1,Forms!frmA!txtEndDate)
=DateAdd("m",-2,Forms!frmA!txtEndDate)
=DateAdd("m",-3,Forms!frmA!txtEndDate)
...
This solution requires no code and will run fairly quickly.

 

[Stephen Aker]

I tried this method and I still can't get what I want in the report. The
report I'm trying to produce has more than one weeks worth of time reported
by day, client and function. I'd like a week's data on each page including
totals by day and week. I can get to one week's report with the method
outlined below, or the code I described in my first post. My problem is
that I can't figure out how to get to the second, third week, et cetera
weeks' reports. I've set up a group on the relative date beginning with
StartDate (there's StartDate and EndDate from the Form).

The crosstab query now makes a dynaset that has D1, D2,...D14 as column
heads. I would like page 2 of the report, or group 2 to contain the second
week of data, D8 through D14, but can't figure out how to make group 2.

I think I'm missing something very obvious and have worked with the
suggestion below for awhile before getting back.

Thanks for the help.

 

[Duane Hookom]

I think you only need to group by the Week of your date field. Would you
mind sharing you recent SQL and how it does and doesn't meet your needs?

--
Duane Hookom
MS Access MVP
--

I tried this method and I still can't get what I want in the report. The
report I'm trying to produce has more than one weeks worth of time
reported
by day, client and function. I'd like a week's data on each page including
totals by day and week. I can get to one week's report with the method
outlined below, or the code I described in my first post. My problem is
that I can't figure out how to get to the second, third week, et cetera
weeks' reports. I've set up a group on the relative date beginning with
StartDate (there's StartDate and EndDate from the Form).

The crosstab query now makes a dynaset that has D1, D2,...D14 as column
heads. I would like page 2 of the report, or group 2 to contain the
second
week of data, D8 through D14, but can't figure out how to make group 2.

I think I'm missing something very obvious and have worked with the
suggestion below for awhile before getting back.

Thanks for the help.

This "canned" response uses months as the column heading date interval.
You
should be able to modify it for dates rather than months. If you have
questions, come on back:

Try not to use "absolute" column headings for dates. You could possibly
use
"relative" dates. For instance:
Form: frmA
Text Box: txtEndDate
Table: tblSales
Field: SaleDate
You want to show 12 months of sales in columns of a crosstab report.
Set the with menuing: Query|Parameter
Forms!frmA!txtEndDate Date/Time

Use this expression for your Column Headings:
ColHead:"Mth" & DateDiff("m",[SaleDate],Forms!frmA!txtEndDate)

This will produce Mth0, Mth1, Mth2, Mth3,... where Mth0 is sales from the
same month as the ending date on your form. Mth1 is the previous month
etc.

Set your queries Column Headings property to:
"Mth0", "Mth1", "Mth2", "Mth3",.., "Mth11"
Build your report based on these "relative" months. If you need column
labels in your report, use text boxes with control sources of:
=DateAdd("m",0,Forms!frmA!txtEndDate)
=DateAdd("m",-1,Forms!frmA!txtEndDate)
=DateAdd("m",-2,Forms!frmA!txtEndDate)
=DateAdd("m",-3,Forms!frmA!txtEndDate)
...
This solution requires no code and will run fairly quickly.

 

[Stephen Aker]

Here is the SQL for the crosstab query:
PARAMETERS [Forms]![StartDialogueBox]![StartDate] DateTime,
[Forms]![StartDialogueBox]![EndDate] DateTime;
TRANSFORM Sum(DailyTotal_Query.Totaltime) AS SumOfTotaltime
SELECT DailyTotal_Query.EACClientNo, DailyTotal_Query.ClientName,
DailyTotal_Query.FunctionCode, DailyTotal_Query.Description
FROM DailyTotal_Query
WHERE (((Format([Date],"Short Date")) Between
[Forms]![StartDialogueBox]![StartDate] And
[Forms]![StartDialogueBox]![EndDate]))
GROUP BY DailyTotal_Query.EACClientNo, DailyTotal_Query.ClientName,
DailyTotal_Query.FunctionCode, DailyTotal_Query.Description
ORDER BY DailyTotal_Query.EACClientNo
PIVOT "d" & DateDiff("d",[Date],Forms!StartDialogueBox!EndDate);

TotalTime is the time by client, function and date; Date is the date;
EACClientNo is the client number; DailyTotal_Query is an intermediate query
to get all the data elements in one table.

I think my problem is in how I built the report. To get the values from
this crosstab into the report I used text boxes with a data source of d1, d2
etc. When the data goes to 2 weeks the first week's data rows will need to
have data sources of d13, d12,...d8. Unless there is a different way to get
the data into the rows.

After grouping the report on [Date] with an interval of a week and then
expanding the data to 2 weeks, the second week client number and functions
gets listed with the first week's and there are no hours for the first week.

I need to be flexible about the number of weeks in the whole
report--sometimes there will be 1, 2, 3 or 4 weeks.

I'm looking for a solution that treats each week as a group and then I can
get group totals as well as a report total. I also thought about using
subreports, but haven't gone down that road yet. I keep thinking
KISS...keep it simple, stupid (to myself). I'm missing the simple solution.

Thanks for the time and attention.

 

[Duane Hookom]

How about setting your column heading expression to:
Expr1: "Day" & Weekday([Date])
Then add a Row Heading of
WeekOf: DatePart("ww",[Date])
Use the WeekOf in the sorting and grouping to separate the first week from
the other weeks.
--
Duane Hookom
MS Access MVP
--

Here is the SQL for the crosstab query:
PARAMETERS [Forms]![StartDialogueBox]![StartDate] DateTime,
[Forms]![StartDialogueBox]![EndDate] DateTime;
TRANSFORM Sum(DailyTotal_Query.Totaltime) AS SumOfTotaltime
SELECT DailyTotal_Query.EACClientNo, DailyTotal_Query.ClientName,
DailyTotal_Query.FunctionCode, DailyTotal_Query.Description
FROM DailyTotal_Query
WHERE (((Format([Date],"Short Date")) Between
[Forms]![StartDialogueBox]![StartDate] And
[Forms]![StartDialogueBox]![EndDate]))
GROUP BY DailyTotal_Query.EACClientNo, DailyTotal_Query.ClientName,
DailyTotal_Query.FunctionCode, DailyTotal_Query.Description
ORDER BY DailyTotal_Query.EACClientNo
PIVOT "d" & DateDiff("d",[Date],Forms!StartDialogueBox!EndDate);

TotalTime is the time by client, function and date; Date is the date;
EACClientNo is the client number; DailyTotal_Query is an intermediate
query
to get all the data elements in one table.

I think my problem is in how I built the report. To get the values from
this crosstab into the report I used text boxes with a data source of d1,
d2
etc. When the data goes to 2 weeks the first week's data rows will need
to
have data sources of d13, d12,...d8. Unless there is a different way to
get
the data into the rows.

After grouping the report on [Date] with an interval of a week and then
expanding the data to 2 weeks, the second week client number and functions
gets listed with the first week's and there are no hours for the first
week.

I need to be flexible about the number of weeks in the whole
report--sometimes there will be 1, 2, 3 or 4 weeks.

I'm looking for a solution that treats each week as a group and then I can
get group totals as well as a report total. I also thought about using
subreports, but haven't gone down that road yet. I keep thinking
KISS...keep it simple, stupid (to myself). I'm missing the simple
solution.

Thanks for the time and attention.

I think you only need to group by the Week of your date field. Would you
mind sharing you recent SQL and how it does and doesn't meet your needs?

 

[Stephen Aker]

It works just great! You are truly an MVP!

One problem left, getting the dates for the TotalTime at the top of each
group. For now I am using the following expression to get the correct start
date at the top of the page:
=Forms!StartDialogueBox!StartDate +
([Weekof]-DatePart("ww",Forms!StartDialogueBox!StartDate))*7

The [Weekof] changes with each group now, but I tried using just [Date] at
the top of each page and the report won't start because of a "" input field.
I tried several other expressions before hitting on this one.

Anything simplier?

Thanks for all your help!!!!

 

[Duane Hookom]

You could add the WeekOf to to 1/1/2004 or some other date. Then add
=WeekOf * 7 + #1/1/2004# +1
=WeekOf * 7 + #1/1/2004# +2
=WeekOf * 7 + #1/1/2004# +3
=WeekOf * 7 + #1/1/2004# +4
etc
You may need to change either the 1/1/2004 or the integer.
--
Duane Hookom
MS Access MVP
--

It works just great! You are truly an MVP!

One problem left, getting the dates for the TotalTime at the top of each
group. For now I am using the following expression to get the correct
start
date at the top of the page:
=Forms!StartDialogueBox!StartDate +
([Weekof]-DatePart("ww",Forms!StartDialogueBox!StartDate))*7

The [Weekof] changes with each group now, but I tried using just [Date] at
the top of each page and the report won't start because of a "" input
field.
I tried several other expressions before hitting on this one.

Anything simplier?

Thanks for all your help!!!!

How about setting your column heading expression to:
Expr1: "Day" & Weekday([Date])
Then add a Row Heading of
WeekOf: DatePart("ww",[Date])
Use the WeekOf in the sorting and grouping to separate the first week
from
the other weeks.