Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Jun 2009
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a report that returns balances by year across a single line

    Name 2003 2004 2005 2006 2007 2008
    User 1 $2,157.94 $583.73 $615.40 $641.57 $685.84 $1,328.84
    User 2 $0.00 $584.82 $616.52 $642.70 $686.99 $1,329.71
    User 3 $0.00 $0.00 $615.40 $641.57 $685.84 $1,328.84
    user 4 $0.00 $0.00 $0.00 $639.81 $684.04 $1,327.49

    I have the data sorting descending by each year. My question is this. Can I have it total every time a $0.00 displays on the subsequent record? For example:

    Name 2003 2004 2005 2006 2007 2008
    User 1 $2,157.94 $583.73 $615.40 $641.57 $685.84 $1,328.84
    Total $2,157.94 $583.73 $615.40 $641.57 $685.84 $1,328.84

    User 2 $0.00 $584.82 $616.52 $642.70 $686.99 $1,329.71
    Total $0.00 $584.82 $616.52 $642.70 $686.99 $1,329.71

    User 3 $0.00 $0.00 $615.40 $641.57 $685.84 $1,328.84
    Total $0.00 $0.00 $615.40 $641.57 $685.84 $1,328.84

    user 4 $0.00 $0.00 $0.00 $639.81 $684.04 $1,327.49
    Total $0.00 $0.00 $0.00 $639.81 $684.04 $1,327.49

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Welcome to the Lounge!

    Create a new query based on the table or query that acts as record source for your report.
    Add all fields or * to the query design grid, then add a calculated field:

    Level: -DSum("[2003]=0 Or [2004]=0 Or [2005]=0 Or [2006]=0 Or [2007]=0 Or [2008]=0", "TableOrQuery", "[Name] <= " & Chr(34) & [Name] & Chr(34))

    where TableOrQuery is the name of the table or query the new query is based on.
    Save this query.
    Open the report in design view.
    Change the record source to the new query.
    In the Sorting and Grouping window, add Level and set the Group Footer property to Yes to make the report group on Level.
    Add text boxes with control source =Sum([2003]) etc. to the group footer.

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

    Thank you for the warm welcome. My table name is data. I tried to replace the "TableorQuery" instance with "data", but I get the following error:

    The expression you entered contains invalid syntax. You may have entered an ooperand without an operator.

    It then highlights "data" in the field. Any ideas?

    Level: DSum("[2003]=0 Or [2004]=0 Or [2005]=0 Or [2006]=0 Or [2007]=0 Or [2008]=0 Or [2009]=0, "data", "[Name] <= " & Chr(34) & [Name] & Chr(34))

    Thanks again!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Sorry, my bad. I forgot a closing quote. I should have been

    Level: -DSum("[2003]=0 Or [2004]=0 Or [2005]=0 Or [2006]=0 Or [2007]=0 Or [2008]=0 Or [2009]=0", "data", "[Name] <= " & Chr(34) & [Name] & Chr(34))

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

    That worked, but I realized, I didn't outline my problem adequately (my apologies). Basically, the years represent outstanding balances. For 2003, there could be 20 users with outstanding balances in 2003. There could be 30 accounts with outstanding balances in 2004, and so on. I'm trying to display a cascading display on the report like so (displaying the years descending, so the balances per year are grouped together correctly...

    Name 2003 2004 2005 2006 2007 2008
    User 1 $100.00 $200.00 $250.00 $300.00 $400.00 $500.00
    User 2 $100.00 $200.00 $250.00 $300.00 $400.00 $500.00
    Total $200.00 $400.00 $500.00 $600.00 $800.00 $1,000.00

    User 6 $----- $100.00 $100.00 $100.00 $100.00 $100.00
    User 7 $----- $100.00 $100.00 $100.00 $100.00 $100.00
    User 8 $----- $100.00 $100.00 $100.00 $100.00 $100.00
    Total $----- $300.00 $300.00 $300.00 $300.00 $300.00

    By looking at the above data, I can easily see my delinquency by year. Basically a detailed aging. The names are unique and do not repeat.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I'll have to think about that - I don't see an immediate solution.

  7. #7
    Star Lounger
    Join Date
    Jun 2009
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Hans. I couldn't think of a solution either:-(

  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    [quote name='wino' post='778810' date='08-Jun-2009 11:07']Thanks Hans. I couldn't think of a solution either:-([/quote]
    The fundamental issue that you have is that you don't know that the subsequent record is zero (or empty) until you process that record. Access processes records in serial order, so the standard reporting tools don't support a two-pass approach to providing totals.

    The only approach I can see that would work would involve processing the records using VBA, and doing all of the report construction either in a temporary table of some sort, and then creating a report based on the table, or by actually creating the report on the fly using Automation and Microsoft Word or Microsoft Excel. Bottom line: it's a very complex reporting structure and you may want to contemplate the value of such a report through a glass of your favorite beverage. (best I could do - no stemmed glassware in the emoticons )
    Wendell

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Now that I've taken a closer look, I find that I don't understand what you want any more, but I think your best bet would be to sort the data in a way that helps you, and to leave it at that.

Posting Permissions

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