Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2002
    Posts
    14

    Unanswered: Year to date by Month

    I have a table with 2 fields: DateEntered and Amount.

    Simply enough I want to create a report based on the total amount per month and have it display:

    Jan $total of [amount]
    Feb $total of [amount]
    March $total of [amount]
    etc....

    Current Year Total [total of column]

    I would also like to show the previous year in the next column laid out in the same format.
    What is the best way to go about it.

    The closest I have is a running query (see below)

    SumOfAmount Expr2 Expr1
    $520.00 2002 1
    $615.00 2002 9
    $665.00 2003 1
    $50.50 2003 2
    $207.00 2003 3
    $333.00 2003 4
    $333.00 2003 5
    $216.69 2003 6
    $105.00 2003 7
    $98.00 2003 8
    $5,572.09 2003 9
    $946.00 2003 10
    $44.00 2003 12

    I'd like to extract this info into a report with the above fromat

    Any ideas?

    Thanks

  2. #2
    Join Date
    Aug 2003
    Location
    Belgrade
    Posts
    68

    Re: Year to date by Month

    If this layout is OK:

    ---------2003 2002
    Jan
    Feb
    March
    April
    ....

    I think it can be done with a crosstab query. Otherwise, I would create a temporary table with these columns:

    2003 Month 2002 Month

    then fill them with data, first for 2003 and then for 2002, and create the report against that table.

  3. #3
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238

    Re: Year to date by Month

    Originally posted by rickyzicky
    I have a table with 2 fields: DateEntered and Amount.

    Simply enough I want to create a report based on the total amount per month and have it display:

    Jan $total of [amount]
    Feb $total of [amount]
    March $total of [amount]
    etc....

    Current Year Total [total of column]

    I would also like to show the previous year in the next column laid out in the same format.
    What is the best way to go about it.

    The closest I have is a running query (see below)

    SumOfAmount Expr2 Expr1
    $520.00 2002 1
    $615.00 2002 9
    $665.00 2003 1
    $50.50 2003 2
    $207.00 2003 3
    $333.00 2003 4
    $333.00 2003 5
    $216.69 2003 6
    $105.00 2003 7
    $98.00 2003 8
    $5,572.09 2003 9
    $946.00 2003 10
    $44.00 2003 12

    I'd like to extract this info into a report with the above fromat

    Any ideas?

    Thanks
    One way of doing this would be to create 2 queries. The first query would look like:

    SELECT JobID, DateEntered, Amount FROM MyTable WHERE (DateEntered Between #01/01/2003# And #31/12/2003#)

    The second query would look like:
    SELECT JobID, DateEntered, Amount FROM MyTable WHERE (DateEntered Between #01/01/2002# And #31/12/2002#)

    The difference is in the year. Then join the 2 queries together to make a third query. You will need to join them, not on the primary key - as this is unique for each record! But on another column such as a job number that wouldn't change over time. You could then build your report of the 3rd query

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •