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;