How to create Outstanding report and Aged Debtors Report

[Sunnylei]

Hi
I have problem with creating outstanding report and aged debtors report. I have created four tables ie Main Contractors, Calims, Payments and Aged Debtors. I also created a Query for Claims. However, I don't know how to creat a report for each subcontract, and aged debtors report. Can anyone help.

Thanks

 

[PNGBill]

You shouldn't need a Aged Debtors Table.
Claims Table is the Charges (Invoices).
Payments Table is the payments made.

Queries and Reports can provide the Aged view of your data.

In Reports you can use Grouping to present the data in Months (if this is the criteria) and show sub totals.
You can set Page Breaks so each month or year is on a new page.

Queries can sort and return totals of Invoices for a month and repayments for a month etc.
We use a Crosstab Query which allows for data to be presented in a style like a spread sheet.

A Union Query first collects all the data from relevant tables and then a crosstab query assembles same for the report.

In your report you could have text box controls that return the number of days from a month to today and give you a age for your debtors.

Sounds simple but will be a little complicated to have it all displayed. More time consuming but once the report is setup, it will do the work for you.

Another option is to export the crosstab query to excel.
We used to do this but found the report method above much easier once it was setup.

Trust this assists.

 

[Sunnylei]

You shouldn't need a Aged Debtors Table.
Claims Table is the Charges (Invoices).
Payments Table is the payments made.

Queries and Reports can provide the Aged view of your data.

In Reports you can use Grouping to present the data in Months (if this is the criteria) and show sub totals.
You can set Page Breaks so each month or year is on a new page.

Queries can sort and return totals of Invoices for a month and repayments for a month etc.
We use a Crosstab Query which allows for data to be presented in a style like a spread sheet.

A Union Query first collects all the data from relevant tables and then a crosstab query assembles same for the report.

In your report you could have text box controls that return the number of days from a month to today and give you a age for your debtors.

Sounds simple but will be a little complicated to have it all displayed. More time consuming but once the report is setup, it will do the work for you.

Another option is to export the crosstab query to excel.
We used to do this but found the report method above much easier once it was setup.

Trust this assists.

Thanks for it. I have used crosstable in query stage. However, I'm not too sure if I also can creat crosstable for outstanding by joining the relevant table. Can you have a look my database.

Thanks

 

[Sunnylei]

Sorry, it should have been 'Crosstab'

 

[PNGBill]

Not able to review your database right now - sorry.

Suggest you first use a union query to collect all your data.
This may depend on how you handle Payments to Invoices but assuming you have data in the two tables that relate ie Debits in one and Credits in the other but both sets of data have Dates and a field that is common ie Unique customer ID.

You would use a Union Query to collect all this data so you effectivly have one group of data with all the records you need for your Aged Debtors report.

Create your crosstab query from the union query and then your report from the crosstab query.

There may well be other options to achieve your result and as I mention above, how you allocate payments to charges may have an effect.

I will try and open your database a little later and in the meantime you may get other advice.

 

[PNGBill]

I recall seeing a lot of posts discussing aged debtors and they refer to having a Table to hold the relationship between transactions.

If you want a true Aged Debtors you need the facility to allocate Payments to Charges.
This can be a little complicated but is achievable.
You need to cater for Payments that part pay one invoice and or more then one invoice.

If you do a search on the forum you should find a few references to this.

Your link would be the Invoice ID so an invoice in January part paid in April will show just the balance due for January and not the full Invoice in January and the payment in April.

All Drs will have a Invoice ID and a Customer ID
All Crs will have a Credit ID and a Customer ID

You show all these records in your Transactions table - fine and they differ by TransactionType and ContractorID.

The additional Table, say Allocations, will hold at least one record for each Payment (TransactionType) and have fileds to hold the data of TransactionID and PayAmount and the net effect will need to be zero ie 1,000 Payment must be allocated to one or more Invoices and the sum of the allocated amounts = 1,000
You could build a form to force this issue when entering a payment.

Your query will then collect the Invoice data and Allocated repayments and return just the underpaid or overpaid invoices to be records for your aged report. Use the Invoice Date and you have the age of the debt.
Date of Payment not really an issue unless you have Unallocated Payments.

Trust this make sence. I haven't used an allocation table as our business doesn't have invoices but there is a lot of reference to this in the forum.