help calculation in Query

[Guest]

Let see if i can explain this right.
I have a file ran from our IT depart that includes all the invoices and all
the payment for all customers. We are doing mass write-offs anything over
181 over.
I want to show actual write-off amnt in my query. For example:

Cust#1, Invoice# 123 $20.00
Cust#1, Invoice# 234 $50.00
Cust#1 , Payment# P123 ( $200.00)
Cust#1, Payment #P222 ($250.00)

I would like to caluclate somehow to show in the query, Cust#1 Payment amt
will be ($70.00 ) on first payment because this is how much invoices can be
applied to the payment and show second payment ast ($250.00) since all the
invoices could be applied to send payment.


so is there a way to do this?

Thank you!

 

[John Vinson]

Let see if i can explain this right.
I have a file ran from our IT depart that includes all the invoices and all
the payment for all customers. We are doing mass write-offs anything over
181 over.
I want to show actual write-off amnt in my query. For example:

Cust#1, Invoice# 123 $20.00
Cust#1, Invoice# 234 $50.00
Cust#1 , Payment# P123 ( $200.00)
Cust#1, Payment #P222 ($250.00)

I would like to caluclate somehow to show in the query, Cust#1 Payment amt
will be ($70.00 ) on first payment because this is how much invoices can be
applied to the payment and show second payment ast ($250.00) since all the
invoices could be applied to send payment.

YOu're assuming that we understand your table structure and your
business rules. Maybe some of the volunteers here might but I
certainly don't. What does "since all the invoices could be applied to
send payment" mean? I simply can't parse this sentence! I'm GUESSING
that the text string

"Payment #P222"

is meaningful in some way but I don't see how it can be linked to any
invoices!

John W. Vinson[MVP]

 

[Guest]

sorry.
Here's the table structure:

Customer # Invoice # Amt
2002374 101001 78.58
2002374 1001579897 -2.47
2002374 1001582065 -259.99

2004472 121456 224.7
2004472 10555989CR -105.86
2004472 1000251079 -553.87

this is how the table looks, it is sorted by customer number so that all the
payment/inv for that cust will be together.

This is what i am hoping I can some how do to my query. I want the result to
look like this:
add another column beside it. Positive # indicates invoice and negative #
indicates payments/credits. Like to take pay/credit amt from invoice # until
balance is 0.

Customer # Invoice # Amt Amt to apply
2002374 101001 78.58 0
2002374 1001579897 -2.47 -2.74
2002374 1001582065 -259.99
-76.11(78.58-2.74)

2004472 121456 224.7 0
2004472 10555989CR -300.86 -224.7
2004472 1000251079 -553.87 0


any help would appreciate it. Thank you!