Page 1 of 1

Aged debtors to excel via Microsoft Query

Posted: 14 Jun 2019, 11:38
by galetaacc
Good afternoon,

Could please anyone help me run aged debtors report from Sage to Excel using Microsoft Query? I linked my Sage (version 25.1) with excel. I struggle when linking the Sales ledger with Audit Header. I choose Acc.reference and Acc name from Sales ledger and then trying to get the aged balances from Audit header but I don't know how to link these two tables. Please help.

Re: Aged debtors to excel via Microsoft Query

Posted: 14 Jun 2019, 11:46
by brucedenney
Well, you kinda can't, but you can!

The aged debtors are calculated based on the outstanding items on each account.

To do this you need to have 2 queries.

The first brings in the account details for each customer.

The next brings in the Audit Headers that have an amount outstanding.

You then need to do some calculations on the Audit Header data to work out the amount outstanding in each of your periods, you can work out the ageing depending on how you want to do it. The way sage does it is based on the settings in Sage and used the AGED values, the problem with this is that they take no notice of the due dates and that some invoice can be on different terms to others, this is where you can do a better job than sage by calculating your aged period based on the days past due, rather than the bucket approach taken by sage.

Then on the Account details you use a sumif to add up the calculated values for the customer on this row from the audit header sheet.

Re: Aged debtors to excel via Microsoft Query

Posted: 14 Jun 2019, 13:07
by galetaacc
I see. Thank you.

1. When you say two queries, do I need to run them separately on different tabs or under one?

2. It is easy to bring up the sales ledger info because all I need is ACCOUNT_REF and NAME. In order to link it with audit header, I believe I have to create the connection. Do I have to link specific fields from the list? I linked NAME from SALES_LEDGER with ACCOUNT_REF from AUDIT_HEADER. Then I added the AGED_30 field into the report but it only created extra lines for existing accounts and under AGED_30 all the values are 0.

3. If the way I'm trying to do this is wrong can I just run a report with all the outstanding invoices per customer and then do my Aged Debtors calculations on the side? Could you please advise how do I run a report with outstanding invoices per customer?

Many thanks in advance.

Re: Aged debtors to excel via Microsoft Query

Posted: 14 Jun 2019, 13:53
by brucedenney
2 separate queries populating 2 separate sheets no need to link in the query.

The audit header can be filtered down to SI and SC and Outstanding >0.00

You could use the AGED_30 etc fields but then you are limited to using Sages method for age rather than working form due dates.

You are not wrong doing it this way but you can run the Aged Debtors report if that is what you want.