Aging 30 60 90

[Guest]

I am trying to make an aging report 30, 30-60, 60-90, >90 days outstanding

I have a field which counts the days since the bill was sent [DaysOut]

Is there a way in the query to make a field for each of the ranges above.

Another words I would like a field where [remainder30] > 30 days,
[remainder60-90] between 60-90 days etc...

If I try to put these definitions in the criteria box it just limits the
whole query.

Currently I have 4 queries each filtered to the ranges above which I have
each on a report as a subreport. Unfortunately due the size of the data it
is very unstable. It takes a long time to load and often locks up my system
when I run this report.

 

[Tom Ellison]

Dear Stefan:

First, I recommend you alter your definitions. The aging brackets should be
defined something like this:

Current (<=30 days)
31-60
61-90
91+

The way you defined them in your post, the categories would overlap. Would
an account what was at 30 days appear in two categories? I think not.

Now, assuming you want an ordinary looking aging report (in my experience),
you have 4 columns in which to place the amounts. Do this with IIF()
functions in your query:

SELECT AccountName, InvoiceNumber, IIF(DateDiff(InvoiceDate, Date()) <= 30,
InvoiceAmount, 0) AS [0-30], IIF(DateDiff(InvoiceDate, Date()) > 30 AND
DateDiff(InvoiceDate, Date()) <= 60, InvoiceAmount, 0) AS [31-60],
IIF(DateDiff(InvoiceDate, Date()) > 60 AND DateDiff(InvoiceDate, Date()) <=
90, InvoiceAmount, 0) AS [61-90], IIF(DateDiff(InvoiceDate, Date()) > 90,
InvoiceAmount, 0) AS [91+]

You can add the tables needed, etc. Replace with your actual column names.

You say you have stored the age of each invoice in DaysOut. This must mean
you calculate this and store it each time you need it. I'm assuming DaysOut
is a column in a table, not in a query. This would be a bad parctice, if
that's what you're doing. Never store a derived value in a table. Always
derive it LIVE each time it is needed. You'll be glad you did. Things work
much better this way.

 

[Guest]

Thank you that worked great!!!
--
Thanks in advance
Stefan

Dear Stefan:

First, I recommend you alter your definitions. The aging brackets should be
defined something like this:

Current (<=30 days)
31-60
61-90
91+

The way you defined them in your post, the categories would overlap. Would
an account what was at 30 days appear in two categories? I think not.

Now, assuming you want an ordinary looking aging report (in my experience),
you have 4 columns in which to place the amounts. Do this with IIF()
functions in your query:

SELECT AccountName, InvoiceNumber, IIF(DateDiff(InvoiceDate, Date()) <= 30,
InvoiceAmount, 0) AS [0-30], IIF(DateDiff(InvoiceDate, Date()) > 30 AND
DateDiff(InvoiceDate, Date()) <= 60, InvoiceAmount, 0) AS [31-60],
IIF(DateDiff(InvoiceDate, Date()) > 60 AND DateDiff(InvoiceDate, Date()) <=
90, InvoiceAmount, 0) AS [61-90], IIF(DateDiff(InvoiceDate, Date()) > 90,
InvoiceAmount, 0) AS [91+]

You can add the tables needed, etc. Replace with your actual column names.

You say you have stored the age of each invoice in DaysOut. This must mean
you calculate this and store it each time you need it. I'm assuming DaysOut
is a column in a table, not in a query. This would be a bad parctice, if
that's what you're doing. Never store a derived value in a table. Always
derive it LIVE each time it is needed. You'll be glad you did. Things work
much better this way.
--
Tom Ellison

I am trying to make an aging report 30, 30-60, 60-90, >90 days outstanding

I have a field which counts the days since the bill was sent [DaysOut]

Is there a way in the query to make a field for each of the ranges above.

Another words I would like a field where [remainder30] > 30 days,
[remainder60-90] between 60-90 days etc...

If I try to put these definitions in the criteria box it just limits the
whole query.

Currently I have 4 queries each filtered to the ranges above which I have
each on a report as a subreport. Unfortunately due the size of the data it
is very unstable. It takes a long time to load and often locks up my system
when I run this report.