Results 1 to 8 of 8

Thread: Report

  1. #1
    Star Lounger
    Join Date
    Jun 2009
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I'm trying to roll-up a few line items in my Access report to a "Prior Balance Due". In a nutshell, I need to roll-up all charges that occurred in the prior month from the report run date into a "Prior Balance Due" line item. For example, if I was running the report for today (12/11/2009), any charges from November would go to the "Prior Balance Due" line item with the sum. Each transaction is a record in the transaction table. Is this possible? Any insight is much appreciated.


    As Is
    11/01/2009 November Monthly Assessment $500
    11/10/2009 Interest $10
    11/10/2009 Late Fee $25
    12/01/2009 December Monthly Assessment $500

    Total $1035


    To Be
    Prior Balance Due $535
    12/01/2009 December Monthly Assessment $500

    Total $1035

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I'd create a query to group the data this way, and use that as record source for the report. See the attached simple demo.

    [attachment=87091:Group.zip]
    Attached Files Attached Files

  3. #3
    Star Lounger
    Join Date
    Jun 2009
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Perfect. What would happen if I have multiple owners. For example, if I added a column with OwnerID to the table?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    What would you want the result to look like? Would you like to group by owner, and display prior balance due and current month data within each owner, or would you like to group by date (aggregating the previous month) and display data per owner within each date, or something else?

  5. #5
    Star Lounger
    Join Date
    Jun 2009
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Hans,

    Group by owner, and display prior balance due and current month data within each owner


    Owner 1
    Prior Balance Due $535
    12/01/2009 December Monthly Assessment $500

    Owner 2
    Prior Balance Due $400
    12/01/2009 December Monthly Assessment $500
    12/15/2009 December Interest $25

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Simply add the Owner ID as the first column of the query:

    [attachment=87158:Group.zip]
    Attached Files Attached Files

  7. #7
    Star Lounger
    Join Date
    Jun 2009
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I thought I had my problem fixed, but it appears the SQL is not takin ginto account prior years and is only rolling up one transaction from the prior month. I've included my database below. Any insight would be much appreciated. Thank you in advance!

    SELECT tblData.OwnerID, IIf(DateSerial(Year([TranDate]),Month([TranDate]),1)=DateSerial(Year(Date()),Month(Date())-1,1),Null,[TranDate]) AS [Date], IIf(DateSerial(Year([TranDate]),Month([TranDate]),1)=DateSerial(Year(Date()),Month(Date())-1,1),"Prior Balance Due",[Tran]) AS Description, Sum(tblData.TranAmount) AS SumOfV
    FROM tblData
    WHERE (((tblData.TranDate)>=DateSerial(Year(Date()),Mont h(Date())-1,1)))
    GROUP BY tblData.OwnerID, IIf(DateSerial(Year([TranDate]),Month([TranDate]),1)=DateSerial(Year(Date()),Month(Date())-1,1),Null,[TranDate]), IIf(DateSerial(Year([TranDate]),Month([TranDate]),1)=DateSerial(Year(Date()),Month(Date())-1,1),"Prior Balance Due",[Tran]);
    Attached Files Attached Files

  8. #8
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Your where clause is:

    Code:
    WHERE (((tblData.TranDate)>=DateSerial(Year(Date()),Month(Date())-1,1)))
    Which limits things to ones where TranDate >= the first day of the previous month this year
    Regards
    John



Posting Permissions

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