Crosstab Report

[T5925MS via AccessMonster.com]

Thank you for taking the time to look into this problem. Using Access2007,
How can I create a report that looks like this?:

‘ ColumnHead
(AuditorID)
‘ Satisfactory
Unsatisfactory
RowHead1(FloorProgName)
RowHead2(FloorProgCriteriaShortDetails) 0 1
RowHead2(FloorProgCriteriaShortDetails) 1 0
RowHead2(FloorProgCriteriaShortDetails) 2 0
RowHead2(FloorProgCriteriaShortDetails) 1 1

The record source is a crosstab query based on a query that contains the
field names above in parenthesis. Here’s the SQL from my crosstab query:

TRANSFORM Nz(Count(qryFloorProgAuditScoreSummary.
FloorProgObservationID),0) AS CountOfFloorProgObservationID
SELECT qryFloorProgAuditScoreSummary.FloorProgName,
qryFloorProgAuditScoreSummary.FloorProgCriteriaShortDetails,
qryFloorProgAuditScoreSummary.Unsatisfactory
FROM qryFloorProgAuditScoreSummary
GROUP BY qryFloorProgAuditScoreSummary.FloorProgName,
qryFloorProgAuditScoreSummary.FloorProgCriteriaShortDetails,
qryFloorProgAuditScoreSummary.Unsatisfactory
PIVOT qryFloorProgAuditScoreSummary.
AuditorID In ('T5925MS','T7847KS','T1234JS');

Here’s the design of my report:

Header
(FloorProgName)
Detail
(FloorProgCriteriaShortDetails) (Unsatisfactory) (AuditorID)

These are the results of my report:

‘ (Unsatisfactory)
(AuditorID)
(FloorProgName)
(FloorProgCriteriaShortDetails) No 0
(FloorProgCriteriaShortDetails) Yes 1
(FloorProgCriteriaShortDetails) No 1
(FloorProgCriteriaShortDetails) Yes 0
(FloorProgCriteriaShortDetails) Yes 1
(FloorProgCriteriaShortDetails) No 2
(FloorProgCriteriaShortDetails) Yes 0

 

[KARL DEWEY]

Maybe something like this --
TRANSFORM Sum(IIF(qryFloorProgAuditScoreSummary.Unsatisfactory=0,1,0)) & "
-- " & Abs(Sum(qryFloorProgAuditScoreSummary.Unsatisfactory)) AS
CountOfFloorProgObservationID
SELECT qryFloorProgAuditScoreSummary.FloorProgName,
qryFloorProgAuditScoreSummary.FloorProgCriteriaShortDetails
FROM qryFloorProgAuditScoreSummary
GROUP BY qryFloorProgAuditScoreSummary.FloorProgName,
qryFloorProgAuditScoreSummary.FloorProgCriteriaShortDetails,
qryFloorProgAuditScoreSummary.Unsatisfactory
PIVOT qryFloorProgAuditScoreSummary.AuditorID In
('T5925MS','T7847KS','T1234JS');