Aging report

[crosspann]

I am new to access and is having a problem creating an aging query that would display the aging criteria for a billing database identifying invoices that are past due 30-59 days 60-90 days etc .

I also have to write a query for the average collection time from the date an invoice is mailed until remittance.

Would appreciate any help.
Thanks

 

[RichMorrison]

Patricia,

We need more detail about what you want a query to produce for aging. But in general..............

If you want the number of invoices in each aging group, you can make a field in a query with a conditional formula. Assuming you know the Age In Days for an invoice:

TotalGroup1:IIf(AgeInDays Between 30 and 59,1,0)
TotalGroup2:IIf(AgeInDays Between 60 and 90,1,0)
etc

These fields contain 1 or zero. In a Group By query, you can Sum the fields.

HTH,
RichM

 

[CJBIRKIN]

Hello

Sorry if i underestimate your knowledge of access here. I need to make some assumptions as well


Assuming the table concerned contains the date the invoice was generated say Invoicedate. What you need to do is add a field to your query in the design view and write the following
in the top line.

invoicedays: datediff("d",[Invoicedate],now())

in the first line of the criteria write
>=30 and <=59

in the second line -which is the or statement- write
>=60 and <=90

What this will do is calculate the number of days between when the invoice was generated and now() the day you run the query.

The criteria will select the records between the two sets of numbers.

For the second question you can use the date diff function for the two fields

[invoice is mailed ] date?
[remittance] date?

again create another field say remitdays:
use the datediff function as above

invoicedays: datediff("d",[Iinvoice is mailed ],[remittance])

and then use the group by facility on the menu in the query design view window -the symbol like a back to front E- change the group by to avg.

This will average the total number of days for all records

Any questions feel free

Chris