Hi All,

Not too great a title, but a little hard to describe in few words.
I have an invoicing system and need to be able to tot up the value of each accounts' invoices prior to the addition of the invoice in question. For example: John has 3 invoices for 100 each and has just received another invoice this week for 100. I need to be able to report based on a date period the value of the account prior to this new invoice (as laid out below).

ID Name InvID InvTotal PreviousValue NewValue
001 John 004 100 300 400

I'm able to get this fine if only one invoice is raised within in the data range, however if 2 or more are raised it will only give the previous value for the latest invoice against all invoices (again, sample below).

ID Name InvID InvTotal PreviousValue NewValue
001 John 004 100 500 600
001 John 005 100 500 600
001 John 006 100 500 600

When what I need is:

ID Name InvID InvTotal PreviousValue NewValue
001 John 004 100 300 400
001 John 005 100 400 500
001 John 006 100 500 600

The query can be parsed using only one table(view) which is a combination of CLIENT and INVOICE (CLIENT_INVOICE - original! )

Any idea how I might accomplish this? I can post my current code if it will help?

Thanks