Cross-tab query-column totals?

[cav]

Can I get the column totals in a crosstab query using the wizard?  No problem setting up the row totals.  If this is impossible, what other way is there to get both totals?  Thanks.

 

[rbowes]

You can not do this in the query view.  You will have to create a form or report to do this.  After creating a form or report(by the way, with the form, set its default view = continuous form).  Place unbound textboxes under the columns you wish to total and set the control source of the textbox = Sum([MyColumn]).  That should work.  

 

[cav]

That works fine but...I am seting this up for an end user who will have to change the dates (column headers) of the cross tab often.&nbsp;&nbsp;<br>I originally set up a parameter query to prompt for Between dates and then ran the cross tab on that query. It would be great if the Report prompted for the dates and then updated the crosstab but I think I'm getting too complicated...and it doesn't seem to work.<br><br>Any further guidance?&nbsp;&nbsp;or do you need more info. Thanks for your help.

 

[rbowes]

Without understanding exactly what your trying to do, I am not sure how to proceed, but here it goes:<br>I understand your problem.&nbsp;&nbsp;In my apps, when a cross tab is required and the criteria for the query is up to the user, I find a way to fix the column headings, therefore set the date to the row heading and the data element that you are measuring to the Column setting and then program into the query in the &quot;Column Headings&quot; row of the query's properties(Click the gray area above the query grid).&nbsp;&nbsp;Another answer may be to make the query a Totals query.&nbsp;&nbsp;That's the route I'd go if I could not set the column headings.<br><br>

 

[elizabeth]

This may be of use - I just posted it in the other crosstab question -<br><br>There is a wonderful but lengthy solution detailed in Alison Balter's Mastering Access 97 in a chapter called &quot;Reports: Advanced Techniques&quot; page 440 called &quot;Building a Report from a Crosstab Query&quot; that builds columns dynamically. Not only does it build the column header at run time, it allows you to define as many columns as fit on the page and hide any unused columns. I used this for a crosstab with Project Managers as a Column Header, as Project Managers come and go frequently. AB has a new book out for version 2000 but haven't bought it yet so don't know if it's in there too.

 

[rbowes]

Elizabeth's suggestion is a good one, I just checked it out and she's right, it's like 4 pages with alot of code, but I plan on using it the next time I need to do a report like that.&nbsp;&nbsp;Thanks for reminding how good that bood is!

 

[cav]

Thanks for your help...I will get the book.