Smart questions
Smart people
 Find A ForumFind An Expert
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Remember Me

Are you a
Computer / IT professional?
Join Tek-Tips now!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Just copy and paste the

Feedback

"...I've gotten solutions within a day - it saved a lot of time and actually got me one ATTABOY from my boss..."

Geography

Where in the world do Tek-Tips members come from?

Dynamic Totals for a Crosstab Query

 Forum Search FAQs Links Jobs Whitepapers MVPs
 RomeERome (Programmer) 31 May 12 12:13
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:

CODE

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.

 dhookom (Programmer) 31 May 12 12:45
 Are we supposed to understand intColumnCount, lngRgColumnTotal(), and conTotalColumns?How are you designing the "dynamics" of the crosstab report?
 RomeERome (Programmer) 31 May 12 16:19
 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 (Programmer) 31 May 12 16:34
 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 (Programmer) 1 Jun 12 0:19
 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?
 RomeERome (Programmer) 6 Jun 12 16:01
 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 (Programmer) 6 Jun 12 17:38
 Glad to here you got this worked out. You basically made a "static" crosstab report from a "dynamic" report.

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!