Need more, need help now? we offer pay-as-you-go support, no contract, tenth of an hour billing, expert help, fast service, no call queue
We can offer lower cost Sage50cloud subscriptions for customer using pay-as-you-go support or who are self-supporting.
Need integration, your shipping system, your website, your invoicing system, your crm.
Need a bespoke solution, Mini Manufacturing, Spreadsheet order import, Back to back orders.
Want web hosting, email, your own domain name, a shop, we do it all!

Aged debtors to excel via Microsoft Query

Report design issues and solutions.
Post Reply
galetaacc
User
User
Posts: 2
Joined: 14 Jun 2019, 11:25
Sage Version: v2018 24 UK/Europe/Africa

Aged debtors to excel via Microsoft Query

Post by galetaacc » 14 Jun 2019, 11:38

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.

User avatar
brucedenney
Site Admin
Site Admin
Posts: 4159
Joined: 28 Feb 2006, 09:56
Sage Version: v2017 23 UK/Europe/Africa

Re: Aged debtors to excel via Microsoft Query

Post by brucedenney » 14 Jun 2019, 11:46

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.
For just about anything Sage :- switch to subscription, pay-as-you-go support, sagecover, upgrades, application integration, reports, layouts, analysis or any other help making life with sage easier/less time consuming Contact me. Image

galetaacc
User
User
Posts: 2
Joined: 14 Jun 2019, 11:25
Sage Version: v2018 24 UK/Europe/Africa

Re: Aged debtors to excel via Microsoft Query

Post by galetaacc » 14 Jun 2019, 13:07

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.

User avatar
brucedenney
Site Admin
Site Admin
Posts: 4159
Joined: 28 Feb 2006, 09:56
Sage Version: v2017 23 UK/Europe/Africa

Re: Aged debtors to excel via Microsoft Query

Post by brucedenney » 14 Jun 2019, 13:53

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.
For just about anything Sage :- switch to subscription, pay-as-you-go support, sagecover, upgrades, application integration, reports, layouts, analysis or any other help making life with sage easier/less time consuming Contact me. Image

Post Reply

Who is online

Users browsing this forum: No registered users and 1 guest