Crosstab Report
in [Reports]
|
Prev: Need subreport column headers to repeat when data goes onto ne
Next: Help needed - want to count categories within a category on a repo
From: T5925MS via AccessMonster.com on 25 Feb 2010 09:32 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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/201002/1
From: KARL DEWEY on 1 Mar 2010 14:40
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'); -- Build a little, test a little. "T5925MS via AccessMonster.com" wrote: > 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 > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/201002/1 > > . > |