Microsoft Access Database Crosstab Report from Pivot Query

Have you ever wanted to create a programmable crosstab report from a crosstab or pivot query? And then found that you need to predefine all the labels and field record sources – which can’t be changed when the names or numbers of returned columns change!
You’ll need to create a new report layout for minor changes to the crosstab results.

We’ve developed a rough working model of a report writing tool.  The setup is from our Order Management Case Study.

We have orders for clothing – tee shirts, sweat shirts of various styles and sizes.  We want to create an order report which lists the sizes as column headings in a multi record continuous detail section of the report.

There are 3 basic problems that need to be solved:

1. Dynamically assign the column labels

2. Dynamically assign the record source of the data fields

3. Sort the order of the columns appropriately.

Let’s show the solution to the 3rd problem first… We have done a crude method for sorting the columns by prefixing a two digit number before each column name – this makes then sort out numerically.  Then before assigning to controls in the report we strip the first two characters for the label fields.  Here’s the lookup table for our sizes in our crosstab report example:

L_Sizes Size_ID Size 1 02Youth Small 2 03Youth Medium 3 04Youth Large 4 01Youth X-Small 5 05Small 6 06Medium 7 07Large 8 08X-Large 9 092X-Large 10 103X-Large 11 114X-Large

Below is the design view of the report:

Note that every label and field are unbound, except for the Sum field at the bottom of the report.  The column labels are in bold.

Here’s the first query which will feed the crosstab/pivot query for the crosstab report:

SELECT order_item_id, L_Sizes.Size, M_order_item_Details.Qty,nz(qty,0) & ” (” & Format([Price],’Currency’) & “)” AS Expr1
FROM L_Sizes INNER JOIN M_order_item_Details ON L_Sizes.Size_ID = M_order_item_Details.Size_Id
UNION select order_item_id,”98Total”,0,format(total_price,’currency’) from q_customer_order_item_totals;

Then comes our crosstab query: (See more detailed explanation of Microsoft Access Crosstab queries.)

TRANSFORM Max(Q_Customer_order_item_Details.Expr1) AS Idetails
SELECT Q_Customer_order_item_Details.order_item_ID
FROM Q_Customer_order_item_Details
GROUP BY Q_Customer_order_item_Details.order_item_ID
PIVOT Q_Customer_order_item_Details.Size;

Note that ‘Q_Customer_order_item_Details’ is the first Select query shown above.

Below is the output from the crosstab query:

Q_Customer_order_item_Details_Crosstab ID 05Small 06Medium 07Large 08X-Large 092X-Large 103X-Large 98Total 1 6 ($6.00) 12 ($6.00) 18 ($6.00) 9 ($6.00) 3 ($8.00)
$294.00 2 2 ($8.75) 3 ($8.75) 4 ($8.75) 3 ($8.75) 2 ($10.75)
$126.50 3 2 ($8.75) 3 ($8.75) 4 ($8.75) 3 ($8.75) 2 ($10.75)
$126.50 4 2 ($8.75) 3 ($8.75) 4 ($8.75) 3 ($8.75) 2 ($10.75)
$126.50 5 2 ($8.75) 3 ($8.75) 4 ($8.75) 3 ($8.75) 2 ($10.75)
$126.50 6

6 ($9.45)

$56.70 8 6 ($9.45)

$56.70 9
4 ($0.00) 3 ($0.00) 5 ($0.00)

$0.00 10
7 ($7.95) 22 ($7.95) 30 ($7.95) 1 ($9.95)
$479.00 11 250 ($9.45) 354 ($9.45) 1440 ($9.45) 450 ($9.45) 48 ($11.45) 40 ($11.45) $24,575.90 13
576 ($9.45)

12 ($11.45)
$5,580.60

We’ll show the visual basic code used to assign the label field values – note that it is attached to the OnFormat event of the Header section:

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim i As Integer
Dim j As Integer
Set db = CurrentDb
Set rst = db.OpenRecordset(“select * from Q_Customer_order_items_Report”)
rst.MoveFirst
j = -1
i = 0
For i = 0 To rst.Fields.Count – 1
If rst.Fields(i).Name Like “*ID” Then GoTo skip_it
j = j + 1
Select Case j
Case 0
me.label0.caption=rst.field(i).Name
Case 1
me.label0.caption=rst.field(i).Name
Case 2
me.label0.caption=rst.field(i).Name
‘ repeat case 3 through your number of labels
.
.
.

End Select
skip_it:
Next
rst.Close
Set rst = Nothing
End Sub
—————————————–

Note that ‘Mid(rst.Fields(i).Name, 3, Len(rst.Fields(i).Name))’ strips off the two digit sorting numbers.

Here’s the code for assigning the Control Source for each of the detail controls (sorry that the code is not indented properly):

Private Sub Report_Open(Cancel As Integer)
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim i As Integer
Dim j As Integer
Set db = CurrentDb
Set rst = db.OpenRecordset(“select * from Q_Customer_order_items_Report”)
rst.MoveFirst
j = -1
i = 0
For i = 0 To rst.Fields.Count – 1

j = j + 1
Select Case j

        Case 0
me.field1.controlsource = rst.field(1).Name
Case 1
me.lable0.controlsource = rst.field(i).Name
Case 2
me.lable0.controlsource = rst.field(i).Name
‘ repeat case 3 through your number of fields
.
.
.

End Select
skip_it:
Next i
rst.Close
Set rst = Nothing
End Sub

———————————————-

You should by able to cycle through the controls using variable control names something like this as a setup:

Dim stReportName as Report
Dim stFieldName as Field

stReportName = “my_report”    ‘ where my_report is
‘ your report name
j = -1
i = 0
For i = 0 To rst.Fields.Count – 1
If rst.Fields(i).Name Like “*ID” Then GoTo skip_it
j = j + 1

stFieldName = “Field” & str(j)

Reports(stFormName).Controls(stFieldName).Label=rst.Fields(i).Name

Next I

Finally, the fruits of our labor:

Remember… this is just one way to solve this crosstab problem – there are likely to be others that are better.  Below is the crosstab report output.

Note that you can use an almost identical method for creating dynamic crosstab forms.

 

Microsoft Office VBA, MS Access 2003, 2007, 2010, 2013, 2016