Dynamic Totals for a Crosstab Query 1

[RomeERome]

Hello All,

I have a crosstab query report in Access 97 that I am currently working on. The data is being displayed correctly on the report, but I can't get the totals to show in a group footer. I get zeroes, but I don't get the summed totals for each column in my report.

It works in the report footer, but not for the group footer. The other variables shown in the code are global variables. The code for the totals is below:

Private Sub GroupFooter0_Print(Cancel As Integer, PrintCount As Integer)
   Dim intX As Integer

   For intX = 2 To intColumnCount
      Me("Tot" + Format(intX)) = lngRgColumnTotal(intX)
   Next intX

   Me("Tot" + Format(intColumnCount + 1)) = lngReportTotal

   For intX = intColumnCount + 2 To conTotalColumns
      Me("Tot" + Format(intX)).Visible = False
   Next intX
End Sub

It's driving me crazy that it's not working properly.

Thanks in advance for your assistance.

 

[dhookom]

Are we supposed to understand intColumnCount, lngRgColumnTotal(), and conTotalColumns?
How are you designing the "dynamics" of the crosstab report?

Duane
Hook'D on Access
MS Access MVP

 

[RomeERome]

Hello Duane,

The intColumnCount represents the column number in my crosstab query, the lngRgColumnTotal() represents the sum of the column numbers, and the conTotalColumns represent the max columns that my crosstab will generate.

The dynamics as far as the column headers and the actual data are all tied to unbound text boxes and the data is being feed in a similar fashion as I was trying to get the column totals. They are working just fine.

 

[RomeERome]

Duane,

It's a great honor to have you responding to my thread. I've seen much of your work. I really appreciate you taking the time to respond to my thread. I'm a decent Access Developer with the desire to always learn more.

 

[dhookom]

I still think we are missing a bunch of code and functionality. Have you considered creating a crosstab report based on my solution at Rogers Access Library?

Duane
Hook'D on Access
MS Access MVP

 

[RomeERome]

Hello Duane,

I figured a great way to make my crosstab report work. It didn't require any code, any changes to my tables. It was only a change to my crosstab query.

Steps are below:

1. I went back to my crosstab query, and in the QBE grid, I right-clicked on the area where you put tables and queries. The query shortcut properties came up, and there is a option under those properties called column headings. So I added the possible column headings for the value items that would show up in my crosstab.
2. Saved my query, and recreated my report.
3. I removed all code, although I like writing code, this was way to difficult to manage with code.
4. It allowed me to have all of the columns that I need for my report. (the other beautiful thing about this was it's still dynamic). If the data isn't there it doesn't show it, and if it is, it does put it in the report.
5. This made totalling my columns so simple.
6. The other thing that it did was stopped the inconsistency that I was experiencing in my report. Sometimes the code would leave off records or leave off headings.

I know it's not quite as cool as being able to hide column headers when there is no data, but it gets the job done for what I needed. It may be a pain for those who have reports with several columns.

Thanks for responding to my thread.

 

[dhookom]

Glad to here you got this worked out. You basically made a "static" crosstab report from a "dynamic" report.



Duane
Hook'D on Access
MS Access MVP