Accounts Receivable Summary

[Julez]

I am wanting to create a report of accounts which are 30, 60 & 90 days past
due. I can create these reports separately, but have not been able to show
the data in columns on one report. See below for an example of what I am
trying to create.

Customer 30days 60days 90days
Cust#1 $100.00 $200.00
Cust#2 $300.00
Cust#3 $20.00

What do I have to do to create this, I have been trying for days to figure
this out.

 

[Allen Browne]

You can get the number of days between the invoice date and today by typing
an expression such as this into a fresh column in the Field row of your
query:
Days: DateDiff("d", [InvoiceDate], Date())

You can then create your 3 columns using expressions such as:
30Days: IIf(Days < 60, [InvoiceAmount], 0)

In practice, the expressions will be more complex than that, if you are
working on calendar months rather than actual 30 day periods, or if you need
to exclude paid invoices, but that's the idea. In some circumstances,
(sorting, grouping, etc), you will also need to replace the reference to
"Days" (the calculated field name) in the 2nd expression with the entire
first expression.

To get the calculated total for each client, you may also need to use that
query as the input for another, or use a subquery. If subqueries are new,
see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066