Aging Buckets in a Query

[ddbrook]

I have a query and it has a service date and amt balance...
I need to take the service date and put the amt balance into different buckets..0-30, 31-60, 61-90, over 120 days.

Example...
if the date of service is aged 37 days the amt balance would go into 31-60 days bucket...

I need some coding help!!!

Could someone please shine the light on this for me??
I would greatly appreciate.

ddbrook

 

[dhookom]

You might want to check out the Partition() function. This works well if all your ranges are the same. If you have different ranges, you may need to use a lookup table that defines the ranges. Use the DateDiff() function to determine the number of days.

Duane
MS Access MVP

 

[BlackBearDude]

The partition function is covered here:

http://www.database-design-2000.com/fudd.htm

you can also do this with your data:

SDATE AMT
10/2/2003 125.00
9/12/2003 650.00
11/1/2003 250.00
4/22/2003 300.00

you can do something like:

SELECT [InvoiceORwhatever], ...,
SUM(IIF(DateDiff(&quot;d&quot;, [sdate], Date())<=30,AMT,0)) AS RANGE1,
SUM(IIF(DateDiff(&quot;d&quot;, [sdate], Date())>30 AND DateDiff(&quot;d&quot;, [sdate], Date())<=60,AMT,0)) AS RANGE2 ...
FROM Table
GROUP BY [InvoiceORwhatever]