creating an aged debtors report 1

[techkenny1]

Hi all,
I am having trouble trying to create an aged debtors report.
The construction of the report is no problem. Its creating the date queries.

All invoices created have the datenow function.
The report needs to show, 90 days, 60 days, 30 days and current.

On the dateraised field in the criteria I have used;
Between now()and now()-30 This will show current debt if I run the report on the last day of the month.
But I have had no luck getting the 90, 60, 30 days to work.
Could I have some ideas please.

kp

 

[dhookom]

A list of the significant fields would help. Also a few sample records and desired display. You can't filter the query to 30 days if you want 90.

You can use a crosstab query that uses a date range table or the Partition function. You can also create a totals query that uses an expression like:

   Sum(Abs(DatField Between Date()-30 and Date()) * NumberField) as Due00_30

Duane
Hook'D on Access
MS Access MVP

 

[techkenny1]

Hi Dookham,
Many thanks for your reply.
The list of fields to display, are companyname, InvT (Invoicetotals)
In the report design, I have used the sorting and grouping with the companyname as the header. In this way the companies are sorted in order.
I'm not sure what the partion function is, but will do some research to find out.
What is required by our finaciers is a report to show each companies debt showing what is current, what is 30 days old, what is 60 days old and what is 90 days old.
As for crosstab queries I will try some ideas with your code.
When you asked for some sample records I'm not sure how to show these to you in this forum

Thank you
kp

 

[dhookom]

kp,
You might try a little harder in your posts to provide some explanation. You mention two fields CompanyName and InvT. Then your "required by our fianciers" suggests possibly two others: a date field and debt.

You didn't take the time to check your spelling or word usage (perhaps English isn't your first language but it should be easy to read posts and copy the spelling): my name (you have also spelled it "Dhookham" in previous thread), partition, financiers, and companies.

To enter some records, just type or copy them like everyone else does.
[tt][blue]
tblInvoices
CompanyName InvoiceDate InvoiceAmt PaymentDate
ABC 2/1/2009 345.20
ABC 12/4/2009 3321.75 1/12/2010
...more records that you type in ...
[/blue][/tt]
Then you use the same method to type in how you want these to display in your report.

That's how you show some time and effort and accuracy so you can expect some time and effort and accuracy from people who might help you.

Also, it helps tremendously if you take the time to learn a little TGML which formats your posts so they are easier to read and understand.

Duane
Hook'D on Access
MS Access MVP

 

[techkenny1]

Hi dhookom,
Accept my apologies for you incorrect spelling of your name.
I'll try to explain what is required as follows;

The accounts table has two fields that require to be reported.
1. Companyname, 2.InvoiceT (which is the invoicetotal)

These are the only fields that are required.Tbl invoice has a Dateraised field that can be used to determine the age of outstanding amounts. for eg: we have one company that owes us money from July09. This would be reported as 90 days debt, even though it is more than 90 days

The bank financiers requires for us to send a report each month showing a report like this;

(tblAccounts)
[blue]Company current 30days 60 days 90 days Total
abc $1000.00 $500.00 $100.00 $50.00 $1650
def $5000.00 $200.00 $100.00 $0.00 $5300
etc[/blue]

The accounts table holds all the data from July09 to June10
The structure of the accounts table is this:
[blue]Companyname is a text field
InvoiceT is a currency field
DateRaised is a date field.[/blue]

Apart from the above I don't know what else to submit

Thanking you
kp

 

[dhookom]

Try a totals query that Groups By Company and uses SQL like:

  Select CompanyName,
  Sum(Abs(DateRaised Between Date()-30 and Date()) * InvoiceT) as Due00_30,
  Sum(Abs(DateRaised Between Date()-60 and Date()-31) * InvoiceT) as Due30_60
FROM Accounts
GROUP BY CompanyName;

Duane
Hook'D on Access
MS Access MVP

 

[techkenny1]

Hi dhookom,
Many thanks for your help. This works very well.
Thankyou
kp