Need to Change An Output Report...

[MJ]

I can picture the desired results and I am drawing a blank on how to make the
current query work to generate the desired output.

I have a database that tracks productivity and variations across my
organization. Each month I produce a report to my director listing
departments who have not approved/acknowledged their monthly numbers.
Current report contains:

DeptCode DeptDesc RevCtr Fac Month Approved ...

ABC First Floor 123 1 10/01/2009 N
DEF First Floor 456 1 10/01/2009 N
ABC First Floor 123 1 09/01/2009 N
GHI Next Floor 789 2 08/01/2009 N

Current query code:

SELECT tblMonthlyData.DEPTCODE, tblMonthlyData.DEPTDESC,
tblMonthlyData.REVCENTER, tblMonthlyData.FACILITY, tblMonthlyData.Month,
tblMonthlyData.Approved, tblMonthlyData.Name,
tblMonthlyData.DateApproved,
tblMonthlyData.RevCtrMap_Dept, tblMonthlyData.DeptCodeMap_Mgr
FROM tblMonthlyData
WHERE (((tblMonthlyData.DEPTDESC) Is Not Null) AND
((tblMonthlyData.REVCENTER) Is Not Null) AND
((tblMonthlyData.Month)<DateSerial(Year(Date()),Month(Date())-1,1)) AND
((tblMonthlyData.Approved)=No))
ORDER BY tblMonthlyData.Month DESC;

It contains more detail than they want and have asked me to simplify it to
something like this:

DeptCode DeptDesc RevCtr Fac 10/2009 09/2009 08/2009 ...

ABC First Floor 123 1 X X
DEF First Floor 456 1 X
GHI Next Floor 789 2 X

While I can visualize the resulting output, I am drawing a bit of a blank.
I am sure that since I already have the above query, I can use it as the
source for the new output.

How can I generate the "fields" that represent the months (going out for
6-12 months, perhaps longer); listing each unique department (using DeptCode;
DeptDesc; RevCtr; and Fac) while placing a "X" in the respective month field
whenever they appear in the source query results?

Thank you in advance,

 

[Duane Hookom]

You can use a crosstab query as the source for your report. There is a
solution at http://www.tek-tips.com/faqs.cfm?fid=5466.

 

[KARL DEWEY]

Use a crosstab query ---
TRANSFORM IIF([Approved] = "N", "X" "") AS Approval_CK
SELECT tblMonthlyData.DEPTCODE, tblMonthlyData.DEPTDESC,
tblMonthlyData.REVCENTER, tblMonthlyData.FACILITY
FROM tblMonthlyData
GROUP BY tblMonthlyData.DEPTCODE, tblMonthlyData.DEPTDESC,
tblMonthlyData.REVCENTER, tblMonthlyData.FACILITY
PIVOT Format(tblMonthlyData.Month, "mm/yyyy") IN("10/2009", "09/2009",
"08/2009");

 

[Steve]

You need a new query. It needs to be a crosstab query. See the Help file for
Crosstab query.

Steve
(e-mail address removed)