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
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    editted because I misread the question.. I'll work on it.

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Ok, the first issue you're looking at is creating an aggregate value by month. You can accomplish this using a combination of a grouping clause by month, and a subselect for previous year(s) values:

    SELECT month(yourDate), SUM(yourCash), (SELECT month(yourDate), SUM(yourCash) FROM yourTable WHERE year(yourDate) = year(date - 1)
    FROM yourTable
    WHERE year(yourDate) = year(date);

Posting Permissions

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