Aging receivable

_hello
This is the first post for me
I have a database for the
Aging receivables

The problem is:When I make run "Qage"query that calculates Aging receivables depends on "id" field "Gltrans"table look in " Q1" query but I want calculates Aging receivables depends on "Opdate"

The difference lies in the following example which i annexed to the bottom
Customer 7 his balance is 3003
As follows details
3000 £ from 1-30 days
3 £ more than 90 days
But when I run "Qage" query the result is
3 £ from 1-30
3000 more than 90 days
Because the transaction dated 1/1/2016 3000£ contain largest number of id then the query consider it last transaction nor consider the date
Thus the result is wrong
I hope Work necessary modification
Thanks

Hi and welcome to the forum.

Change the Qout in Q1 to:


The format bit in the middle is to ensure the date is treated in US format. The ## are to tell the query that the input is a date.

Hope that helps.

Thank you a lot
That's ok

Excuse me
I face another problem
The expression in field"deen" in query "Q1" work's wrongly ,if I use this case
Customer1 his transaction as following
1.in 1-1-2016 invoice no-1- amount (10£)
2.in 1-3-2016 he paid (11£)
3.in1-4-2016 invoice no-2- amount (10£)
another invoice At the same date no -3- amount (100£)
Then his balance must be(109£) but the system calculated his balance(110)
*Because if there were two invoices at the same date the expression (qout)will sum two invoices as number one
And therefore the exprrssion(deen) will not work as well
I need a way to solve this problem and change (qout) to sum two or three or.... Invoices have same date as Cumulative amount nor one amount
According to the previous example
10. 1-1-2016
100. 1-4-2016
110. 1-4-2016
Not
10
120
120

Or any one has other idea to calculate "aging receivable"
I hope find reply as before
I have attached a sample.

My previous post has been modified because of the wrong understanding of the problem.
I wait your replies

i have figured it out
by create query '7'
but i have about 45000 records then the the query 'qage' became somewhat cumbersome because to run 'qage' need to run 3 queries before
do you have any advice to me ?