Aging Report with Line Item Summaries

[eRed]

I need to create an Aging Report.



The clients have two types of dollar amounts due (FIELDS: "NAME", "TYPE", "AMT_DUE", "DUE_DATE" ).


The first is a single line item, one time charge (TYPE=4000),
The second is a series of amounts in the form of monthly payments (TYPE = 5000).


I also have some columns that are Calculated (in an associated query)


Current Report:

[SIZE=2]LINE   NAME          TYPE    #       AMT_DUE      DUE_DATE      DAYS    Future    0-30      >30
       (key)        (Text)         (Currency)     (Date)      (Calc)   (Calc)   (Calc)    (Calc) 
1      John Doe      4000            $1,000        8/10/2010    115                       $1,000
2      John Doe      5000    1         $200        9/10/2010     84                         $200
3      John Doe      5000    2         $200       10/10/2010     54                         $200
4      John Doe      5000    3         $200       11/10/2010     23               $200         
5      John Doe      5000    4         $200       12/10/2010     -7      $200                  
6      John Doe      5000    5         $200        1/10/2011    -38      $200                    

                                                                        ______   _____    ______ 
                                                          TOTALS         $400     $200    $1,400  
[/SIZE][SIZE=2]
[/SIZE]

Desire Report:

[SIZE=2]LINE   NAME          TYPE    #       AMT_DUE      DUE_DATE      DAYS    Future    0-30      >30
       (key)        (Text)         (Currency)     (Date)      (Calc)    (Calc)   (Calc)    (Calc) 
1      John Doe      4000            $1,000      8/10/2010      115                       $1,000
2**    John Doe      5000   [2]        $400                    [>30]                        $400
4      John Doe      5000    3         $200     11/10/2010       23               $200          
5**    John Doe      5000   [2]        $400                    [< 0]     $400                    
                                                                         ____     ____    ______  
                                                           TOTALS        $400     $200    $1,400  
[/SIZE][SIZE=2]
[/SIZE]

** Indicate that these rows are the summary of "like" rows summing the Amount Due, Aging and Counting the Payment "#" Field.

I don't know how begin to create this Report. Can someone give me an idea how to achieve this, or point me in the right direction.

Thank you
eRed

 

[vbaInet]

It's hard to tell what you're trying to achieve because the text isn't aligned properly. Maybe show a full screen shot of your current report in print preview mode and briefly explain what you would like to happen.

 

[eRed]

will do thanks for the feedback

 

[eRed]

I cleaned up the original post to make it readable.

 

[vbaInet]

Ok, but you haven't explained how you happen to merger records 2 and 3, and records 5 and 6. What fields make them similar? Plus how did you derive at the figures in the DAYS field in 2** and 5**?

 

[eRed]

Thank you for reading the post.

Records 2 - 6 are payment TYPE = 5000.

Records 2&3 are both in the >30 Column (there Days old are over 30 - "Now()-DUE DATE").

Records 5&6 are both in the Future Column (there Days old are less than 0 - "Now()-DUE DATE").

Is they a way to apply something like:

If payment TYPE = 5000 And DAYS > 30
Then SUM AMT_DUE
COUNT payment #

If payment TYPE = 5000 And DAYS < 0
Then SUM AMT_DUE
COUNT payment #

or a way to create a "GROUPING" of the records that meet two conditions (TYPE And DAYS).

Thanks again.

eRed

 

[vbaInet]

It's not as easy as that. Post your db with some sample data of at least 20 records and I will come up with something.