How to Create Aging Reports in Access
An aging report is a report that shows accounts receivable amounts grouped by age. The report makes it easy to spot customers who are late with their payments. The key to creating these types of reports in Microsoft Access is to make a view of your data that has these columns already created. This is done using a powerful query language technique called a sub-select.
Other People Are Reading
Instructions
-
-
1
Use the DateDiff function to calculate just how late any one payment is. As a very simplified example, suppose there are two tables in the Access database: Orders and Customer. Each customer has a name and an ID. Each order has an ID, a customer ID, an amount, an order date and a payment date. If the order is not yet paid, the payment date is null. The number of days that the order payment is late is the number of days between today and the order date. To get this value in an Access query, use the DateDiff function. An example would be DateDiff("d", OrderDate, Now). The "d" tells the function to count the number of days (as opposed to months, years, hours, minutes, etc.). The "Now" in the function call indicates the current date according to the computer's clock. A sub-select is a separate select query nested within an existing select query. The sub-select must return only one value. Here is an example of a query with a sub-select that gives just the orders less than 30 days late:
SELECT c.custname,
(SELECT sum(o.Amount) FROM Orders o WHERE datediff("d", o.OrderDate, now) < 31 AND o.PaymentDate is NULL AND o.CustomerID = c.ID) as [30 Days or Less],
FROM Customer c ORDER BY c.CustName -
2
Combine multiple sub-queries into a single large query. By combining these types of sub queries, each with a different set of criteria, a set of columns is created where each column has the total amount of payments for a given customer that are late by the specified number of days. Here is an example:
SELECT c.custname,
(SELECT sum(o.Amount) FROM Orders o WHERE datediff("d", o.OrderDate, now) < 31 AND o.PaymentDate is NULL AND o.CustomerID = c.ID) as [30 Days or Less],
(SELECT sum(o.Amount) FROM Orders o WHERE datediff("d", o.OrderDate, now) > 30 AND datediff("d", o.OrderDate, now) < 61 AND o.PaymentDate is NULL AND o.CustomerID = c.ID) as [31-60 Days],
(SELECT sum(o.Amount) FROM Orders o WHERE datediff("d", o.OrderDate, now) > 60 AND datediff("d", o.OrderDate, now) < 91 AND o.PaymentDate is NULL AND o.CustomerID = c.ID) as [61-90 Days],
(SELECT sum(o.Amount) FROM Orders o WHERE datediff("d", o.OrderDate, now) > 90 AND datediff("d", o.OrderDate, now) < 121 AND o.PaymentDate is NULL AND o.CustomerID = c.ID) as [91-120 Days],
(SELECT sum(o.Amount) FROM Orders o WHERE datediff("d", o.OrderDate, now) > 120 AND o.PaymentDate is NULL AND o.CustomerID = c.ID) as [121 Days or More]
FROM Customer c ORDER BY c.CustNameThis query will give 6 columns: Customer Name, total amount less than 30 days late, amount between 31 and 60 days late, 61 and 90 days, 91 and 120 days, and more than 121 days late. Once the query is set up the way it needs to be, save it in Access.
-
-
3
Create the report. A saved query like the one above can be used as the basis of a report just as if it were a table. The report wizard can be used, selecting the saved query instead of the tables.
-
1
Tips & Warnings
Do not allow Access to auto-generate a report on the saved query. The auto-generate feature adds grouping that will cause an error when run. Use the report wizard instead.
