VBA code for pivot table in report

[Johnny Drama]

Hello all,

Hoping you can help me with a little problem I've been trying to figure out, but can't.

I'm wanting to customize the "Yearly Sales Report" from the MS Northwind Traders DB. The existing report totals the sales by quarter and groups by employee, but why I'm trying to do is create a second sales column for different sales for each employee during the quarter. Essentially ending up with two numbers for each quarter for each employee. I've attached an excel spreadsheet so you can get an idea of what I'm trying to do. The code from the report is below.

Private Sub Report_Open(Cancel As Integer)
On Error GoTo ErrorHandler

Dim strSQL As String

If IsNull(TempVars![Display]) Or IsNull(TempVars![Group By]) Or IsNull(TempVars![Year]) Then
DoCmd.OpenForm "Sales Reports Dialog"
Cancel = True
Exit Sub
End If
strSQL = "TRANSFORM CCur(Nz(Sum([Sales]),0)) AS X"
strSQL = strSQL & " SELECT [" & TempVars![Display] & "] as SalesGroupingField FROM [Sales Analysis] "
strSQL = strSQL & " Where [Year]=" & TempVars![Year]
strSQL = strSQL & " GROUP BY [" & TempVars![Group By] & "], [" & TempVars![Display] & "]"
strSQL = strSQL & " Pivot [Sales Analysis].[Quarter] In (1,2,3,4)"

Me.RecordSource = strSQL
Me.SalesGroupingField_Label.Caption = TempVars![Display]

Done:
Exit Sub
ErrorHandler:
' Resume statement will be hit when debugging
If eh.LogError("Yearly Sales Report_Open", "strSQL = " & strSQL) Then
Resume
Else
Cancel = True
End If
End Sub

I know I need to create another line with strSQL = "TRANSFORM CCur(Nz(Sum([Sales]),0)) AS X" where sales would be the second field to be calculated, but I can't figure out how to make them both appear on the report.

Thanks in advance for any guidance!

 

[Ranman256]

Build a 'report' table that looks like your final output. You then run a series of append queries to add the pivot table to the columns in the 'report' table.
qaAddData1
qaAddData2
...
then once all the pivot tables are in the 'report' table, make a query to sum the columns / person.
This is your final display report.

 

[Johnny Drama]

Hummm...not exactly sure how to perform what you are suggesting.