Add column totals to a crosstab query

Question

I don't even know if this is a possibility, but thought I would try anyhow.

I have a need to display the results of a crosstab query with some additional information.  The results of the crosstab will be change from time to time.  I have explored trying to dynamically format a report, but it would appear that that technique is too involved for my skill set. There are actually 14 separate crosstabs that need to be displayed.  The user would actually like the results each displayed on a separate tab such as would appear in an excel spreadsheet.  I have it functioning as desired using separate forms on each tab.   That will be fine until the output changes.

Actually simply displaying the crosstab in data sheet view would be the best except for a couple of things.  One, how do I prevent the user from changing the data in a displayed query?  Second is there any technique I could use to come up with column totals? 

The number of columns and names of the columns change sometimes.  Display of the crosstab itself would deal with this.  If I could just deal with the two issues above.

Example of what I am looking for appears above.  My Query yields A1 through E4 and beyond.  If I could get it to display A5 and beyond and keep the user from making changes, that would solve it?  Any ideas?

I thought about using microsoft query to pull the data from the crosstab into an existing excel spreadsheet that had a row at the top that would sum each column, but you have to specify what columns in the Query you want to import.  So that won't work either.

Answer 1

Hello tkosel,

>>The user would actually like the results each displayed on a separate tab such as would appear in an excel spreadsheet.

As far as I know, crosstab query does not provide such function to add a sum/count. As a workaround, I would suggest you connect the table in excel and then show the data in pivot table format.

Here is the example.

Data Source:

<tfoot></tfoot>

Table4

Name	City	Value
Name1	City2	1
Name2	City3	2
Name3	City4	3
Name1	City1	4
Name2	City2	5
Name3	City3	6
Name1	City4	7
Name2	City1	8
Name3	City2	9
Name1	City1	10

Pivot Table Set:

Best Regards,

Terry

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 2

Terry,

Thanks for your suggestion.  While mulling it over, I don't think this will work either since I would have to define the fields I want, and due to the nature of the crosstab query, I never know for sure what the field names will be, or how many there will be.  Perhaps I do not fully understand your suggestion.

I do have a solution although there are some things I do not like about it.

I use the TransferSpreadsheet command to create a excel spreadsheet from my crosstab query.  I then insert a row at the top of my newly created spreadsheet.  I am pretty sure there will never be more than 11 columns in the spreadsheet, and the first column is names, so I don't need any totals there.  I then populate Cells B1:M1 in the spreadsheet with formulas:   XlSheet.Range("B1") = "=Sum(B4:B1000)"

Of course I do a different formula for each column.  I know there will never be more than 300 rows, but just to be on the safe side, went with 1000.

Now, on my form, I create a unbound object frame from my excel spreadsheet and link it.  Of course this is kind of clumsy since you can only fit the entire object if the frames size mode is set to stretch and that makes it really small if you have lots of rows and columns.  The good thing is that the user is really only interested in the totals at the top.  If s/he wants, s/he can double click and the spreadsheet will open in Excel, where s/he can scroll around to see the whole thing.  Only problem here is that I cannot get excel to open full screen, it only opens in a window.  No big deal, s/he can maximize it.  The otherr problem with this method is that the user could edit the values in the spreadsheet.

I think this will work for me pretty good.  See any problems that I have missed?

Answer 3

Hi tkosel,

Since you used TransferSpreadSheet command to export the query to workbook, I think you could also try to put the row at the end of the data range. You could try to get the last row index and then add the row to next index

A simply code is

lastRowIndex = XlSheet.Cells(XlSheet.Rows.Count, 1).End(xlUp).Row
XlSheet.Range("A" & (lastRowIndex + 1)).Value = "Totals"
XlSheet.Range("B" & (lastRowIndex + 1)).Formula = "=Sum(B2:B" & lastRowIndex & ")"
...

Anyway, I'm glad to hear that you have solved the issue. I would suggest you mark your solution as answer to close this thread.  Thanks for understanding.

Best Regards,

Terry

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 4

Terry,

I could use the crosstab query to add that totals column too.  However, the user doesn't want that column, so I took it out of the query.  Good suggestion though!

Terry, I didn't read your response carefully enough.  I misunderstood.  I will try it and see how it looks.  The main reason I like my solution is because to display the spreadsheet in access, I have to use a unbound object frame and you cannot scroll to the bottom if object is too large for the frame.  With totals at the top, they will always be visible without having to shell to the linked object.  I like some other things in your suggestion like the code so I don't have to arbitrarily pick a number for number of rows.  "

lastRowIndex"

Is there a similar command like "lastColumnIndex"

Answer 5

Hello tkosel,

For lastColumnIndex, please check

lastColumnIndex = XlSheet.Cells(1, XlSheet.Columns.Count).End(xlToLeft).Column

Best Regards,

Terry

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].