# Thread: Formula needed (2000)

1. ## Formula needed (2000)

Hi!!

I have a query with the following SQL:

SELECT tblBilling.AccountID, tblBilling.DateWorked, tblBilling.InvoiceTotal, tblBilling.InvoiceNo, tblBilling.DateBilled, tblPayment.DatePaid, tblPaymentAllocations.InvoiceNoReference, tblPaymentAllocations.AmountAllocated, [invoicetotal]-[amountallocated] AS Balance
FROM tblPayment RIGHT JOIN (tblDemographics LEFT JOIN (tblBilling LEFT JOIN tblPaymentAllocations ON tblBilling.InvoiceNo = tblPaymentAllocations.InvoiceNoReference) ON tblDemographics.AccountID = tblBilling.AccountID) ON tblPayment.PayID = tblPaymentAllocations.PayID
ORDER BY tblPayment.DatePaid;

This works fine to show the balance left after X amount has been allocated.

I need to be able to see the overall balance left for the invoice number
after each allocation.

For example, if invoice # 201 = \$300.00 and there are PaymentAllocations
each month in the total of \$50.00 each..............I need the query to show
the overall balance in succession (if that makes sense). I haven't
got a clue as to how to write this one.

Thanks,
Leesha

2. ## Re: Formula needed (2000)

Does tblPaymentAllocations contain a field or fields with info about the schedule of payments, for example dates, or start date and interval?

3. ## Re: Formula needed (2000)

Hi Hans,

No, tblAllocations contains info re the amount allocated and references the invoice number. tblPayment is the table that contains data regarding date paid.

Leesha

4. ## Re: Formula needed (2000)

Does the following do what you want?

SELECT tblBilling.AccountID, tblBilling.InvoiceNo, tblPaymentAllocations.PayAllocID, tblBilling.InvoiceTotal, tblPaymentAllocations.AmountAllocated, [InvoiceTotal]-[AmountAllocated]-Val(nz(DSum("AmountAllocated","tblPaymentAllocatio ns","InvoiceNoReference=" & [tblPaymentAllocations].[InvoiceNoReference] & " AND PayAllocID <" & [tblPaymentAllocations].[PayAllocID]),0)) AS Balance
FROM tblPayment INNER JOIN (tblBilling INNER JOIN tblPaymentAllocations ON tblBilling.InvoiceNo = tblPaymentAllocations.InvoiceNoReference) ON tblPayment.PayID = tblPaymentAllocations.PayID
ORDER BY tblBilling.InvoiceNo, tblPaymentAllocations.PayAllocID;

5. ## Re: Formula needed (2000)

Hi Stranger!

I'll give it a shot.

Leesha

6. ## Re: Formula needed (2000)

All I can say is .................OMG! I'm in awe. It worked beautifully!

Thanks,
Leesha

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•