Crosstab in a Report

[Ray1127]

I have a table in Access 2000 that stores the data in the following format:

group
case description
quarter
Count
pmpy(per Member Per yer)

I need to create a crosstab in a report that shows in the following format for each group

2008 2008 2008
Qtr2 Qtr 3 Qtr 4
case description cnt 2 0 1
pmpy 3.91 4.10 3.87

The columns can change depending on the date range for example the above was 4/1/2008 through 12/31/2008

If I use 4/1/2008 through 3/31/2009 it would show 2008 Qtr 2 through 2009 Qtr 1

created 4 text field and in the open event of the report set the 4 text fields to the 4 quarters in the report.

In the group footer I have 4 text fields with the formula
=SUM(IIF([qtr]=[txtqtr1],[ct],0))

for each of the 4 quarters txtqtr2,txtqtr3,txtqtr4

the problem is that everytime I run the report I get prompted for txtqtr1 Even though I've verified that txtqtr1 is set in the report open event. What am I missing?

 

[dhookom]

Check out faq703-5466. You should be able to change months to quarters or any other date interval.

Duane
Hook'D on Access
MS Access MVP