Statement ageing date totals

[jasn_78]

Ok what I am trying to is I have an account statement that works fine except the client now wants to see 30/60/90 day totals for the accounts,

90+ days: Total purchases over 90 days old less payments made since then
31-60 days: Total purchases over 30 days old and less than 60 days old less payments made since then (less the money used to offset 90 day old accounts)
1-30 days: Total purchases less than 30 days old less payments made since then (less the money used to offset 90 day old accounts and the 30-60 day accounts)

the attached image is the table where all account transactions are made, it is easy enough for me to get all purchases between a period but what I am having problems with is then calculating after purchases etc are taken into account. Any suggestion on writing this query would be great, btw I cannot change the format of the db as it is a 3rd party piece of software and I am just trying to write reports that read information from it.

Jason

 

[namliam]

I dont quite follow your problem, making the totals for <30, 30-60 and 90+ days (what no 60-90 days??) is quite easy using a group by and crosstab query...

But what do you mean with the payments?

 

[jasn_78]

namliam, yes that is to include payments, and sorry i wrote that wrong it is meant to be current(0-30) 31-60 61-90 and 90+

hope that makes more sense

 

[namliam]

You can use something like this:
IIf(Int((Date()-[acop_sysdate])/30)>=3,"90+",Format(Int((Date()-[acop_sysdate])/30)*30+1,"000") & " - " & Format((Int((Date()-[acop_sysdate])/30)+1)*30,"000"))

To figure out your periods

However I have no clue as to how to seperate the payments from the purchases, or how to "link" the payments to its proper purchase.
I understand you want a payment that was done yesterday ago, for a purchase of 35 days ago to be deducted from the total outstaning for 30-60.