Help with a Monthly Report Design and Layout

Discussion in 'Microsoft Access' started by MJ, Jan 8, 2010.

  1. MJ

    MJ Guest

    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
     
    MJ, Jan 8, 2010
    #1
    1. Advertisements

  2. MJ

    Duane Hookom Guest

    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
     
    Duane Hookom, Jan 8, 2010
    #2
    1. Advertisements

  3. MJ

    MJ Guest

    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
     
    MJ, Jan 8, 2010
    #3
    1. Advertisements

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. JR
    Replies:
    0
    Views:
    260
  2. Guest
    Replies:
    2
    Views:
    320
    John Vinson
    Jan 12, 2005
  3. Guest
    Replies:
    2
    Views:
    1,871
    Albert D. Kallal
    Mar 20, 2007
  4. Frances

    Report design view. Layout / Move up a section

    Frances, Apr 7, 2009, in forum: Microsoft Access
    Replies:
    7
    Views:
    441
    Guest
    Apr 10, 2009
  5. CW

    Design and layout for Order Analysis?

    CW, Mar 10, 2010, in forum: Microsoft Access
    Replies:
    5
    Views:
    155
    joelgeraldine
    Mar 17, 2010
Loading...

Share This Page