Sum for aging

[iccpm]

I am trying to set up an aging query, to show me the totals for a currency amount invoiced in a date range as a function of the current date, i.e. less than 30, 30 to sixty..etc. I wrote a query to do a datediff to come up with the age. Problem is, my boss wants all the totals displayed horizontally, rather than grouped all the less than 30s, then all the 30 -60s, so I need to have a query as the record source that has all those numbers figured already- HELP! [sig][/sig]

 

[WP]

I have encountered this problem many times. There are a couple of ways to do this. One is to create sub reports for each group or to build a summary table at report runtime of the categories and then use the summary table as the reports' recordsource. I prefer the later because it gives much better performance.

Summar Table

LT30
BETW30_60
BETW61_90
etc

So to fill the summary table

Public Sub BuildSumm()
Dim rsIn as Recordset
Dim rsSumm as Recordset

set rsIn=Currentdb.Openrecordset("YourQuery&quot
set rsSumm=Currentdb.Openrecordset("YourSummmaryTable&quot

Currentdb.execute "DELETE FROM YourSumaryTable;"

do until rsIn.eof
rsSumm.AddNew
If rsIn!yourfield_For_Days<31 then
rsSumm!LT30=rsIn!InvoiceAmountField
ElseIf rsIn!yourfield_For_Days>30 and rsIn!yourfield_For_Days<61
rsSumm!BETW31_60=rsIn!InvoiceAmountField
ElseIf rsIn!yourfield_For_Days>30 and rsIn!yourfield_For_Days<61
rsSumm!BETW61_90=sIn!InvoiceAmountField
Else
rsSumm!GT90=sIn!InvoiceAmountField
Endif
rsSumm.Update
rsIn.MoveNext
loop

rsIn.Close
rsSumm.Close

Set rsIn=Nothing
Set rsSumm=Nothing

End Sub

So code to run report would be

Call BuildSumm
Docmd.openreport ...

You can now have a detail report of Sums or Counts.

WP [sig]<p>Bill Paton<br><a href=mailto:william.paton@ubsw.com>william.paton@ubsw.com</a><br><a href=

http://www.neptune400.co.uk/>http://www.neptune400.co.uk/</a><br>

Check out my website ![/sig]

 

[WP]

Oops! should have read the above first !

In case you don't spot it second Elseif shoul be

ElseIf rsIn!yourfield_For_Days>60 and rsIn!yourfield_For_Days<91


WP [sig]<p>Bill Paton<br><a href=mailto:william.paton@ubsw.com>william.paton@ubsw.com</a><br><a href=

http://www.neptune400.co.uk/>http://www.neptune400.co.uk/</a><br>

Check out my website ![/sig]