Help with a Monthly Report Design and Layout

M

MJ

I have a bit of a challenge in front of me with a requested monthly report
that will translate a list of departments that have not approved their
monthly numbers in our databases.

Query:
SELECT tblMonthlyData.DEPTCODE, tblMonthlyData.FACILITY, tblMonthlyData.Month
FROM tblMonthlyData
WHERE (((tblMonthlyData.Approved)=No))
GROUP BY tblMonthlyData.DEPTCODE, tblMonthlyData.FACILITY,
tblMonthlyData.Month
HAVING (((tblMonthlyData.Month)<DateSerial(Year(Date()),Month(Date())-1,1)))
ORDER BY tblMonthlyData.DEPTCODE, tblMonthlyData.Month DESC;

Generates a list of departments without approvals:
DEPTCODE FACILITY Month
3CSE S 11/01/2009
3CSE S 10/01/2009
3CSE S 09/01/2009
3CSE S 08/01/2009
3CSW S 11/01/2009
3CSW S 10/01/2009
3CSW S 09/01/2009
3CSW S 08/01/2009
3CSW S 10/01/2009
4CCL S 11/01/2009
4CCL S 10/01/2009
4CCL S 09/01/2009
4CCL W 09/01/2009
4CCL S 08/01/2009 ...

Desired Output Layout:
DEPTCODE FAC 12/2009 11/2009 10/2009 09/2009 ...
3CSE S NO YES YES YES ...
3CSW S NO YES YES YES ...
4CCL S NO YES YES YES ...
4CCL W NO NO YES NO ...

Would it be smarter to create a table for this layout, or can a report be
created that is smart enough to adjust the column headings on a monthly
basis? How can I best approach this?

Thank you in advance for your time and help with this.
 
M

MJ

Duane, I fully expected you to be the first to reply on this one and you came
through!

I am a little embarrassed about it, because within a minute of the time I
submitted this question I was already looking at some of your previous
crosstab solutions and suggestions.

The crosstab definitely was the answer to to question.

Thanks again for your inputs.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top