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.
Need more, need help now?
- - - - - - - - - - - - - - - - - - pay-as-you-go support - no contract - tenth of an hour billing - expert help - fast service - no call queues
Need integration?
- - - - - - - - - - - - - - - - - - with your shipping system - website - invoicing system - crm - cms - manufacturing - order import - back to back orders..
Need a report?
- - - - - - - - - - - - - - - - - - Excel reporting that pulls data from Sage - custom layouts - layouts that change adapt to your brands and/or for drop shipping.
Want web hosting?
- - - - - - - - - - - - - - - - - - Your own domain name - email - a shop - wordpress - woo commerce - ticket systems - help desks - forums - portals
- - - - - - - - - - - - - - - - - - pay-as-you-go support - no contract - tenth of an hour billing - expert help - fast service - no call queues
Need integration?
- - - - - - - - - - - - - - - - - - with your shipping system - website - invoicing system - crm - cms - manufacturing - order import - back to back orders..
Need a report?
- - - - - - - - - - - - - - - - - - Excel reporting that pulls data from Sage - custom layouts - layouts that change adapt to your brands and/or for drop shipping.
Want web hosting?
- - - - - - - - - - - - - - - - - - Your own domain name - email - a shop - wordpress - woo commerce - ticket systems - help desks - forums - portals
Aged debtors to excel via Microsoft Query
- brucedenney
- Site Admin
- Posts: 4629
- Joined: 28 Feb 2006, 09:56
- Sage Version: v28 UK/Europe/Africa
Re: Aged debtors to excel via Microsoft Query
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.
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 :- Discount subscriptions, pay-as-you-go support, application integration, reports, layouts, linked excel spreadsheets, analysis or any other help making life with sage easier/less time consuming Contact me.
Re: Aged debtors to excel via Microsoft Query
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.
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.
- brucedenney
- Site Admin
- Posts: 4629
- Joined: 28 Feb 2006, 09:56
- Sage Version: v28 UK/Europe/Africa
Re: Aged debtors to excel via Microsoft Query
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.
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 :- Discount subscriptions, pay-as-you-go support, application integration, reports, layouts, linked excel spreadsheets, analysis or any other help making life with sage easier/less time consuming Contact me.
Who is online
Users browsing this forum: No registered users and 10 guests