How to create an Access crosstab query with totals for the columns AND the rows?

Question

I want my query result to look like this:

          Person1 Person2 Person3 Person4    Total 
Status1         2       4       7       3      16
Status2         0       1       0       3      4
Status3         0       0       0       0      0
Status4         0       1       3       0      4
Total           2       6       10      6      24

I'm able to get everything except that bottom row with:

TRANSFORM Count(personName) 
SELECT status, Count(status) AS Total
FROM table1 
GROUP BY status
PIVOT personName

I found something about using a UNION to tack on the last row, but I can't seem to quite get that right. Seems like this should be a common activity.

The result I'm looking for is the table I posted at the top. The query below it doesn't include the bottom row. — Joshua Stafford, Apr 25 '11 at 19:28

score 5 · Answer 1 · answered May 31 '13 at 14:11

5

There is actually a simple solution to this issue. Once you have designed your crosstab query, go into design mode within the query and select "Totals" in the Records section on the Home tab. Then you can select the Sum or Count etc....

Here is a link that gives steps: http://office.microsoft.com/en-us/access-help/display-column-totals-in-a-datasheet-HA001233062.aspx

answered May 31 '13 at 14:11

Lydia

51
1
1

Can someone show an example of how to do this through VBA? I want to control it in code because the number of columns returned by a Crosstab query are variable and can't always be configured in a designer. – Ben Sep 06 '18 at 02:06
This is what to use for a fixed number of columns. Thanx for posting! – pakx Apr 12 '19 at 16:12

score 5 · Accepted Answer · answered Apr 25 '11 at 18:44

5

You'd basically have to run your query twice - once to get the data and then a second time to provide the aggregates. If you're set on doing this, make the first query to return data its own object. Then make another query to aggregate the first one another object. Create a final third query object to combine the two using a UNION as you mentioned.

Although I have to say I don't really recommend this. It sounds like you're trying to force the SQL to generate something that's really presentational information (i.e. it doesn't belong in the same dataset).

answered Apr 25 '11 at 18:44

Yuck

49,664
13
105
135

I agree that a union is a hacky way to do it. The crosstab query gets me 90% of the way there and if I only needed to run it once, Access generates a fine looking report based on that. However, this will need to be run many times and I don't want the users generating their own reports using the report wizard. So instead of generating labels and text boxes at run time, the datasheet view would suffice. – Joshua Stafford Apr 25 '11 at 19:30
3

If it's for reporting purposes only, why not just use a summary footer? You can set the value of a textbox in the footer to be =Sum(person1) for instance. – Yuck Apr 25 '11 at 19:42
It is for reporting purposes only. There will be a variable number of text boxes though depending on how the report is generated. Thinking about layout out the boxes with labels at run time makes me cringe. – Joshua Stafford Apr 25 '11 at 19:49
+1 for the point about presentation-layer considerations -- this doesn't belong in the SQL statement. – David-W-Fenton Apr 26 '11 at 00:21
That being said, would you generate labels and text boxes at run time to show all of the totals? If so, does anyone have an example of having done this? – Joshua Stafford Apr 26 '11 at 13:51
This KB article explains how to create a dynamic crosstab query - http://support.microsoft.com/kb/328320. I also found a blog post (http://www.techrepublic.com/article/create-access-reports-from-crosstab-queries/6129954) that might be helpful. In the end you'll have to write some code to turn on/off the labels and text boxes based on the shape of the result set. – Yuck Apr 26 '11 at 14:07
1

@HuckIt, there's a practical limit to what you can do dynamically; while you can lay out text boxes as needed for the person columns in your query, beyond a certain point you won't be able to display them neatly, especially if the report is to be printed. If you can set a limit, then you don't need to create the labels and boxes at run time, just create the max you'll display in design view and hide or show them as needed at run time. – Dave DuPlantis Apr 26 '11 at 18:36
Dave's remarks nicely summarize the general difficulty in dealing with crosstab / pivot queries. – Yuck Apr 26 '11 at 18:40
I decided to put the information I wanted into a separate subreport. It's not as pretty, but that's life. – Joshua Stafford Apr 27 '11 at 19:18

score 0 · Answer 3 · edited Oct 06 '15 at 21:23

I've been looking for a solution too. Could not find one either except writing a query based on the crosstab and then summing that one and adding in to the bottom in a union query. Since I try to do all SQL statements from inside a form (more manageable to deploy) I do not like this approach: writing or refilling a Querydef/view from code etc.

If you display the results in a subform on your form, you might do the following:

below the subform, and another subform short enough to hold only 1 record.

Bind the controls in the form to a function as follows:

control1 = fnADOSum(yourCrosstabfield1, yourCrosstabSQL) 

Public Function fnADOSum(fldName As String, strInputSQL As String) As Double
    On Error GoTo ERRHANDLER

    Dim RS1 As ADODB.Recordset
    Dim cnn As ADODB.Connection
    Dim StrSQL As String
    Dim dblRunTot As Double


    Set RS1 = New ADODB.Recordset
    RS1.CursorLocation = adUseServer
    Set cnn = CurrentProject.Connection

    dblRunTot = 0

    With RS1
    .Open strInputSQL, cnn, adOpenForwardOnly, adLockReadOnly
        If Not .EOF And Not .BOF Then
            .MoveFirst
            Do Until .EOF
            dblRunTot = dblRunTot + Nz(.Fields(fldName).Value, 0)
            .MoveNext
            Loop
        End If
    .Close
    End With

    fnADOSum = dblRunTot

    'CLEAN UP:
    cnn.Close
    Set RS1 = Nothing
    Set cnn = Nothing


    EXITHANDLER:
    Exit Function

    ERRHANDLER:
    '' your own error handling proc
    '' LogError err.Number, err.Description


End Function

deGuza · Answer 4 · 2017-08-01T17:51:06.797

0

Lydia wrote: "There is actually a simple solution to this issue. Once you have designed your crosstab query, go into design mode within the query and select "Totals" in the Records section on the Home tab. Then you can select the Sum or Count etc...."

Going into the design mode did not work for me:

I ran the query.
Then went to the Home tab
Selected Totals in the Records section
The label "Totals" appeared at the bottom of the Crosstab query results, but no actual totals yet.
Clicked on the empty cell to the right of the Totals label.
An arrow appeared, and I chose "Sum".

[I am using Access 2013]

edited Aug 01 '17 at 17:51

answered Aug 01 '17 at 17:43

deGuza

65
6

2

This should be a comment – Dov Benyomin Sohacheski Aug 01 '17 at 18:10
Can't appear to save this, though – ElphiusMostafa Jun 14 '23 at 22:14

score 0 · Answer 5 · answered Jan 25 '21 at 22:28

Found after much trial and error...

To toggle the totals rows ON when you enter a form or sub-form you can add the following VBA in the form's code:

Private Sub YourFormName_Enter()
    If Application.CommandBars.GetPressedMso("RecordsTotals") = False Then
        Application.CommandBars.ExecuteMso "RecordsTotals"
    End If
End Sub

How to create an Access crosstab query with totals for the columns AND the rows?

5 Answers5

Linked