Solved

Aging Report

Posted on 1998-08-29
5
467 Views
Last Modified: 2006-11-17
How do I create an aging report in V8?
0
Comment
Question by:rtstannard
  • 3
5 Comments
 
LVL 1

Expert Comment

by:rene_moeller
ID: 1960652
Could you be a bit more specific?
0
 
LVL 4

Accepted Solution

by:
tomook earned 100 total points
ID: 1960653
The general technique is to add a calculated field to a query which determines the "bucket" for each record, then run GROUP BY on that field. To make this simple, you can use two queries, one to calculate the bucket and a second to group.

For example, given the tables and fields:
Customers
   CustID
   CustName
Invoices
   InvID
   CustID
   AmountDue
   Age

Make Query1:
SELECT InvID, CustID, AmountDue, Age,
IIF(Age <= 30, "Current", IIF((Age > 30) And (Age <= 60) , "30-60", "Over 60") As AgingBucket
FROM Invoices;

And Query2:
SELECT CustID, Sum(AmountDue) As BucketTotal, AgingBucket
FROM Query1
GROUP BY CustID, AgingBucket;

0
 
LVL 4

Expert Comment

by:tomook
ID: 1960654
I should note that you can structure things a little differently, which helps you print standard invoices or statements.

Query1:
SELECT InvID, CustID, AmountDue,
  IIf(Age<=30, AmountDue, 0.0) As BucketCurrent,
  IIf((Age>30) And (Age<=60), AmountDue, 0.0) As Bucket30,
  IIf((Age>60) And (Age<=90), AmountDue, 0.0) As Bucket60,
  IIf(Age>90, AmountDue, 0.0) As BucketOver90
FROM Invoices;

Query2:
SELECT CustID,
  Sum(BucketCurrent) As BucketCurrentTotal,
  Sum(Bucket30) As Bucket30Total,
  Sum(Bucket60) As Bucket60Total,
  Sum(BucketOver90) As BucketOver90Total
FROM Query1
GROUP BY CustID;

Query2 will show you one record per customer, which makes certain reports easier to write.
0
 

Author Comment

by:rtstannard
ID: 1960655
tomook:  Thanks for a full, understandable, and syntactically correct answer.  Good work!


0
 
LVL 4

Expert Comment

by:tomook
ID: 1960656
I am glad there were no syntax errors as I just typed it in cold. Thanks.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now