Crosstab Query Date Range

[AuditorCMM]

We use a database for employees to enter the time they spend each day on a
particular project. Currently, I have a crosstab query set up to give me a
report like this:
John Mary Dave
Project #1 10 5 20
Project #2 15 10 10
etc...

The problem is, we need to report this monthly. In the past, I've used
simple queries with the date criteria Between [Beginning Date:] And [Ending
Date:] so that the user can enter the particular date range the report is
needed for. I have tried this with the crosstab, but cannot find a way to
make it work. Any suggestions? I really want to avoid having to set up a
different query & report for each month.

Thanks!

 

[Allen Browne]

You can use parameters in a crosstab, as long as you declare them. In query
design view, choose Parameters on the Query menu, and enter 2 rows in the
dialog:
[Beginning Date:] Date/Time
[Ending Date:] Date/Time

Another fairly simple way around this issue is to add a couple more fields
to the crosstab. In the Field row, enter:
TheYear: Year([Date1])
substituting your date field name for Date1.
Accept Group By in the Total row, and choose Row Heading for Crosstab.

In the next column:
TheMonth: Month([Date1])
with the same settings.
The query now generates monthly figures for each month. You can filter the
report for the month you want.

More suggestions:
Crosstab query techniques - row totals, zeros, parameters, column
headings
at:
http://allenbrowne.com/ser-67.html

Another sample:
dynamic monthly crosstab reports
at:
http://www.access.hookom.net/DynamicMthlyCrosstabRpt.htm

 

[AuditorCMM]

That worked great. Thank you so much!!

You can use parameters in a crosstab, as long as you declare them. In query
design view, choose Parameters on the Query menu, and enter 2 rows in the
dialog:
[Beginning Date:] Date/Time
[Ending Date:] Date/Time

Another fairly simple way around this issue is to add a couple more fields
to the crosstab. In the Field row, enter:
TheYear: Year([Date1])
substituting your date field name for Date1.
Accept Group By in the Total row, and choose Row Heading for Crosstab.

In the next column:
TheMonth: Month([Date1])
with the same settings.
The query now generates monthly figures for each month. You can filter the
report for the month you want.

More suggestions:
Crosstab query techniques - row totals, zeros, parameters, column
headings
at:
http://allenbrowne.com/ser-67.html

Another sample:
dynamic monthly crosstab reports
at:
http://www.access.hookom.net/DynamicMthlyCrosstabRpt.htm

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

We use a database for employees to enter the time they spend each day on a
particular project. Currently, I have a crosstab query set up to give me
a
report like this:
John Mary Dave
Project #1 10 5 20
Project #2 15 10 10
etc...

The problem is, we need to report this monthly. In the past, I've used
simple queries with the date criteria Between [Beginning Date:] And
[Ending
Date:] so that the user can enter the particular date range the report is
needed for. I have tried this with the crosstab, but cannot find a way to
make it work. Any suggestions? I really want to avoid having to set up a
different query & report for each month.

Thanks!

 

[Steve]

Create a query with the date criteria Between [Beginning Date:] And [Ending
Date:] and then create a crosstab query that is based on the first query.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)