PC Review


Reply
Thread Tools Rating: Thread Rating: 2 votes, 5.00 average.

Help with a Monthly Report Design and Layout

 
 
MJ
Guest
Posts: n/a
 
      8th Jan 2010
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.

--

MJ
 
Reply With Quote
 
 
 
 
Duane Hookom
Guest
Posts: n/a
 
      8th Jan 2010
I would take a look at the dynamic monthly crosstab report solution at
http://www.tek-tips.com/faqs.cfm?fid=5466.

--
Duane Hookom
Microsoft Access MVP


"MJ" wrote:

> 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.
>
> --
>
> MJ

 
Reply With Quote
 
 
 
 
MJ
Guest
Posts: n/a
 
      8th Jan 2010
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.

--

MJ


"Duane Hookom" wrote:

> I would take a look at the dynamic monthly crosstab report solution at
> http://www.tek-tips.com/faqs.cfm?fid=5466.
>
> --
> Duane Hookom
> Microsoft Access MVP
>
>
> "MJ" wrote:
>
> > 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.
> >
> > --
> >
> > MJ

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Help with a Monthly Report Design and Layout MJ Microsoft Access Queries 2 8th Jan 2010 07:13 PM
Help with a Monthly Report Design and Layout MJ Microsoft Access Reports 2 8th Jan 2010 07:13 PM
Choosing Layout: Css-Layout or Table-Layout hpourfard@gmail.com Microsoft ASP .NET 1 19th Jun 2006 11:06 AM
layout design - layout tables or layers? =?Utf-8?B?cmFuY2gtY3V0dGVy?= Microsoft Frontpage 5 10th Jan 2005 04:18 PM
Help! Help! Help! Help! Help! Help! Help! Help! Help! Help! Help! Help! Help! -$- Windows XP Internet Explorer 2 21st Dec 2003 11:45 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 09:13 PM.