If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
help calculation in Query
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! |
#2
|
|||
|
|||
On Thu, 23 Jun 2005 08:26:05 -0700, "GEORGIA"
wrote: 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] |
#3
|
|||
|
|||
sorry.
Here's the table structu 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! "John Vinson" wrote: On Thu, 23 Jun 2005 08:26:05 -0700, "GEORGIA" wrote: 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] |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Adding tables in SELECT query, messes up Sum calculation | Darren Kozey via AccessMonster.com | New Users | 5 | April 26th, 2005 09:16 PM |
User-defined calculation as text in an update query | Eric Kelso | Running & Setting Up Queries | 4 | March 2nd, 2005 06:22 PM |
adding 2 fields including null entries | Jesse | Running & Setting Up Queries | 26 | January 18th, 2005 05:31 PM |
Too Few Parameters error Mail Merge Access Parameter Query | Tony_VBACoder | Mailmerge | 3 | September 14th, 2004 12:15 PM |
Query Calculation Doesn't Get Put Into Table | Doug MacDonald | Running & Setting Up Queries | 3 | September 7th, 2004 01:13 AM |