CrossTabs & Aging Reports

  • Thread starter Thread starter Jim Ridge
  • Start date Start date
J

Jim Ridge

Hi,

I am trying to create a Crosstab query for a A/R Report that will show the
total accounts receivable for the company name as row headings and "1-30
days", "31-60 days", "61-90 days", and ">90 days" as the column headings.

I have the data in days overdue. How do you group and limit the column
headings for above? Is it possible? I can use the DatePart function for
grouping in months but that is not exactly what I need.

Thanks,
Jim
(e-mail address removed)
 
Dear Jim:

Since you are not using a variable set of columns, either in number,
name, or in criteria, you could write this as a fixed set of columns
by subquery instead of using a crosstab.

SELECT AccountNumber, AccountName,
(SELECT SUM(AmountOwed) FROM YourTable T1
WHERE T1.AccountNumber = T.AccountNumber
AND DaysPastDue < 1) AS Current,
(SELECT SUM(AmountOwed) FROM YourTable T1
WHERE T1.AccountNumber = T.AccountNumber
AND DaysPastDue BETWEEN 1 AND 30) AS C1TO30,
(SELECT SUM(AmountOwed) FROM YourTable T1
WHERE T1.AccountNumber = T.AccountNumber
AND DaysPastDue BETWEEN 31 AND 60) AS C1TO30
FROM YourTable T
ORDER BY AccountNumber

The above is very rough, as I do not know the names of your columns or
tables.

Also, it would be very smart to store the due dates rather than store
(and therefore have to update) the days past due. Something with the
DateDiff function could replace DaysPastDue in the above and be more
flexible and more powerful.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top