Dynamic cross-tab (and report)

[Egg 'n' Bacon]

It's been so long since I've used a cross-tab query, that I've forgotten most of what I knew. So I'm hoping one of you nice people can help me out.

In a nutshell, I need to create a matrix type report (to replace a manualley entered spreadsheet) that shows who have been trained in various skills (by selected dept). It only has to show that they have been trained, not any other data.

My problem starts with not being able to use a combo box as a criteria selector, for the cross-tab query (get an error about the combo box [CboDeptName]not being recognised). The second issue is the report creation; how is a cross-tab style report created?

This is the query (sorry about all the relationships);

Code: Select all

TRANSFORM Sum(TblAttendance.Version) AS SumOfVersion
SELECT [FirstName] & " " & [LastName] AS Name
FROM (tblDept INNER JOIN TblRole ON tblDept.DeptName = TblRole.Dept) INNER JOIN ((TblPerson INNER JOIN (TblCourse INNER JOIN TblAttendance ON TblCourse.CID = TblAttendance.CourseID) ON TblPerson.PersID = TblAttendance.PersonID) INNER JOIN tblPersonRoles ON TblPerson.PersID = tblPersonRoles.PersID) ON TblRole.RoleID = tblPersonRoles.RoleID
WHERE (((tblDept.DeptName) Like [forms]![frmDeptMatrixSel]![CboDeptName]))
GROUP BY [FirstName] & " " & [LastName]
PIVOT TblCourse.CName;

[HansV]

In a crosstab query, you must explicitly declare the parameter, otherwise it won't be recognized. There are two ways you can do this:

1) In design view
- Open the query in design view.
- In Access 2003 or before, select Query | Parameters...
- In Access 2007 or later, activate the Design tab of the ribbon, and click Parameters.
- Enter the parameter exactly as used in the Criteria line, in your query: [forms]![frmDeptMatrixSel]![CboDeptName]
- Specify the data type, in your query: Text.
- Click OK.

2) In SQL view
- Insert the following line at the top of the SQL of the query:

PARAMETERS [forms]![frmDeptMatrixSel]![CboDeptName] Text ( 255 );

[HansV]

The attached sample database (in Access 2000 format) shows one possible way to create a dynamic crosstab report. I originally posted it in Access 97 format in Woody's Lounge, many years ago.

CrosstabReport.zip

Another approach is shown in CrossTabReport.mdb (intermediate) on Roger Carlson's site.

[Egg 'n' Bacon]

Thank you Hans.

Well the parameters part was a nice easy fix, the second part is going to take a bit more work I might be coming back on that bit (eventually)

Cheers

[HansV]

A dynamic crosstab report is a lot of work, but very nice once you get it running...

[Egg 'n' Bacon]

I'm having second thoughts on using a report for this; the Column headings are a bit lengthy and unless Access now supports rotated text, I might just export to a spreadsheet.

[JohnH]

You can use Vertical Headings for reports. Labels/Text Boxes have a Vertical Property.

vetticalHeadings.gif

[Egg 'n' Bacon]

Excellant!

Now that's sorted I just need to clear out the bugs from my code.
I'm getting a coupel of messages saying it won't work. Because "Item not found in the collection", followed by "Access does not recognise " as a valid field, name or expression"

Here's my code;

Code: Select all

Private Sub Report_Open(Cancel As Integer)
  Const conNumColumns = 20
  Dim qdf As DAO.QueryDef
  Dim rst As DAO.Recordset
  Dim intColumnCount As Integer
  Dim intX As Integer
'
'  ' Don't open report if frmEmployeeSales is not loaded.
'  If Not IsLoaded("frmEmployeeSales") Then
'    Cancel = True
'    MsgBox "Please open this report from frmEmployeeSales.", vbExclamation
'    Exit Sub
'  End If
'
  On Error GoTo Handle_Err

  ' Set record source.
  RecordSource = "QryDeptMatrix"
  ' Open QueryDef object.
  Set qdf = CurrentDb.QueryDefs("QryDeptMatrix")
  ' Set query parameters based on values in form.
  qdf.Parameters([Forms]![frmDeptMatrixSel]![CboDeptName]) = [Forms]![frmDeptMatrixSel]![CboDeptName]
  'qdf.Parameters("Forms![frmEmployeeSales]!txtEndDate") = Forms![frmEmployeeSales]!txtEndDate

  ' Open recordset.
  Set rst = qdf.OpenRecordset
  ' Don't open report if there are no data.
  If rst.RecordCount = 0 Then
    MsgBox "No records found.", vbInformation
    Cancel = True
    GoTo Handle_Exit
  End If

  ' Fix number of columns in crosstab query and limit to max available.
  intColumnCount = rst.Fields.Count - 1
  If intColumnCount >= conNumColumns Then
    intColumnCount = conNumColumns - 1
  End If

  For intX = 1 To intColumnCount
    ' Set caption of label in page header to field name.
    Me("txtHeading" & intX).Caption = rst(intX).Name
  Next intX

  ' Set control source of first text box in detail section to row header.
  Me("txtColumn1").ControlSource = "=[" & rst(1).Name & "]"

  ' Start totals in column 2 (the first column with a crosstab value).
  For intX = 2 To intColumnCount
    ' Set control source of text box in detail section to fraction of column total; replace nulls by 0.
    Me("txtColumn" & intX).ControlSource = "=Nz([" & rst(intX).Name & "],0)/Sum([" & rst(intX).Name & "])"
  Next intX

  DoCmd.Maximize

Handle_Exit:
  On Error Resume Next
  rst.Close
  Set rst = Nothing
  Set qdf = Nothing
  Exit Sub

Handle_Err:
  MsgBox Err.Description, vbExclamation
  Resume Handle_Exit
End Sub

[HansV]

Temporarily make the line

On Error GoTo Handle_Err

into a comment by inserting an apostrophe ' before it. Now when you try to open the report, you'll be given the option to Debug; if you select this, the line causing the problem will be highlighted, and you can inspect the value of variables by hovering the mouse pointer over them.

What do you find?

[Egg 'n' Bacon]

OK the first error (item not found) is relating to the following line;
qdf.Parameters([Forms]![frmDeptMatrixSel]![CboDeptName]) = [Forms]![frmDeptMatrixSel]![CboDeptName]

Hovering the mouse produces;
[Forms]![frmDeptMatrixSel]![CboDeptName] = "Medicool"

This value is appropriate, if "medicool" is put into the query (instead of the [forms]![etc) the result is the same.

[HansV]

Any chance that you could attach a stripped down and zipped copy of the database?

[Egg 'n' Bacon]

Here you go.

Copy of TrainingDB.zip

[JohnH]

This problem is just missing quote marks!

Code: Select all

qdf.Parameters("[Forms]![frmDeptMatrixSel]![CboDeptName]") = [Forms]![frmDeptMatrixSel]![CboDeptName]

But this leads to another problem which I can't track down at the moment!

Errormessage.gif

[Egg 'n' Bacon]

Ah, the original code had a mixture of quotes and square brackets (in the parameters section).

I noticed that enclosing the second part of that line in quotes and removing the square brackets results in a 'no records found error', even when there are some???

[JohnH]

Also the query has Department as a Group By field, but not as a Row Heading. Should it be the first Row Heading?

[Egg 'n' Bacon]

Hi John, the Department field is really only there to filter the records to specified departments. Access added the second iteration of that field.

[JohnH]

What do you want to appear in the body of the report?

The fields are set to percentage at the moment, and the code sets the control source to be the value of the field divided by the sum of that field. Do you want that?

But the problem is not with the code. If I fix the query to just Warehouse, then bind the report to the query, and deactivate all the code, I still get the same error.

Also you should not use Name as a field name. Name is a Reserved Word. Use PersonName (or something similar) instead.

[HansV]

John has already pointed out some of the problems, in particular the lack of quotes around the parameter name.

But the next culprit was hard to find: the report was sorted on CategoryName (in the Sorting and Grouping window) but this field doesn't occur in the data source (it was a remnant of the original demo). This caused the second error message. Clearing the sort order makes the report work - more or less.

But the code was specific to a question in Woody's Lounge; it can be simplified a bit here. See the attached version.

Copy of TrainingDB.zip

[Egg 'n' Bacon]

Wow!

I'm glad you guys are around; I would'nt have stood a chance at sorting that out.

Thank you both

[Egg 'n' Bacon]

Bit of a re-visit; I am trying to exclude some results i.e. those in field 'CName' beginning with 'SWP'.

Trouble is I can't seem to find the correct sytax.

Here's the query (with the latest attempt in place)

Code: Select all

PARAMETERS [forms]![frmDeptMatrixSel]![CboDeptName] Text ( 255 );
TRANSFORM Max(IIf([TblCourse]![Version] Is Not Null,"1"," ")) AS V
SELECT [FirstName] & " " & [LastName] AS [Employee Name]
FROM (tblDept INNER JOIN TblRole ON tblDept.DeptName = TblRole.Dept) INNER JOIN ((TblPerson INNER JOIN (TblCourse INNER JOIN TblAttendance ON TblCourse.CID = TblAttendance.CourseID) ON TblPerson.PersID = TblAttendance.PersonID) INNER JOIN tblPersonRoles ON TblPerson.PersID = tblPersonRoles.PersID) ON TblRole.RoleID = tblPersonRoles.RoleID
WHERE (((TblCourse.CName)<>"SWP" & "*") AND ((tblDept.DeptName)=[forms]![frmDeptMatrixSel]![CboDeptName]))
GROUP BY [FirstName] & " " & [LastName], TblPerson.LastName
ORDER BY TblPerson.LastName
PIVOT TblCourse.CName;