Crosstab query subreport in report footer problem 1

[Eupher]

I have a report that lists items like so:
[tt]
Unit Name 1 Weekend
-----------------------------------
Event Description 1 X
Event Description 2
Event Description 3 X

Unit Name 2 Weekend
-----------------------------------
Event Description 1 X
Event Description 2 X[/tt]

In the report's footer section, I have a sub-report based on a crosstab query that totals it all up, like so:
[tt]
Unit Weekend Non-weekend Total
------------------------------------------
ABCDE 4 16 20
FGHIJ 7 14 21
KLMNO 3 9 12[/tt]

Here's the problem: if the detail section of the report works out such that the report footer is the only section on the last page (i.e. no records on the last page, only the totals subreport), the report footer prints, but all the numbers are zeros. If even one record is on the last page, the subreport prints correctly. How do I fix this? Thanks!

Ken S.

 

[dhookom]

Do you have any values in the Link Master/Child properties? Does your crosstab have column headings set in the properties?

Have you considered changing your crosstab to a standard totals query?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Eupher]

Hi, Duane,

No, no values in Link Master/Child. Yes, crosstab does have column headings set. I will experiment with a standard totals query and see what I come up with. And of course I could build a VBA function that would give me what I want, but that seems rather the long way around.

Funny thing is, everything works exactly right, except in the very rare circumstance where the report footer ends up as the only item on the last page.

Thanks for the tips, I'll post back once I get it fixed or if I run into more snags.

Ken S.

 

[dhookom]

Your query might work better as
SELECT UNIT, Sum(Abs([WeekEnd]="X")) As WeekEnd, Sum(Abs(IsNull([WeekEnd]))) As NonWeekEnd, Count(*) as Total
FROM tblYourTable
GROUP BY UNIT;

I'm not sure this will make any difference but it might be worth trying. If it doesn't work, I would take a look at Keep Together and Force Page properties.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Eupher]

Thanks again, Duane, I will try your query. I had already looked at Keep Together and Force Page. Keep Together had no effect on the problem. Force Page works; but that guarantees that the subreport will always be on a new page - a possible solution, but would prefer to avoid that.

Ken S.

 

[Eupher]

Duane, changing to a standard totals query did the trick. Thanks for the help.

Ken S.