How to calculate a balance as of a certain date for a particular customer?

[Marsha McCarthy]

Question.

I have a database with two tables:

Table 1 - Invoice Table

- Transaction ID (Auto Number)
- Invoice Number
- Invoice Date
- Invoice Amount

Table 2 - Invoice Payment Table

- Invoice Payment Reference ID (Auto Number)
- Transaction ID (linked to Table 1)
- Payment Amount
- Payment Date

I want to be able to pull a customer balance as at a particular date by placing a date filter in the query. Say for instance Transaction ID 1 has an invoice #3 invoice date March 3 2011 and invoice amount $400.00. The customer made a payment of $20 on March 3, $50 on March 4 and $60 on April 1. I want to be able to pull the customer balance as at any date and it reads the correct thing...so say I pull the customer balance as at March 31 the customer balance should be $270.

What I have done thus far, was to create a cross tab query. I am able to pull the balances as at a particular date as I created a field in the master query named "date criteria" which is used as the filter as it is a combination of the payment date and invoice date...based on the later date. However, in the crosstab query the invoice amount is duplicated each time a payment is made.

Is there a way to create a running balance on a transaction id so that i could pull the balance for a customer at any given point in time?

[ADezii]

1. Given the Data that you supplied, the actual Balance should be $330 and not $270, namely:
   

   $400 - ($20 on 3/3 + $50 on 3/4) = $330
   'The payment of $60 on April 1, is after the posted date of 3/31/2011.

2. This being said, I have arrived at an awkward solution, but nonetheless a solution so far.
3. I'll post the SQL, Function Declaration, as well as the Demo that I used for this Thread.
4. View this as only a Temporary Solution until a better one is forthcoming.
   

   SELECT [Invoice Table].[Transaction ID], [Invoice Table].[Invoice Amount], [Enter Date] AS [Date], 
   fCalcBalance([Invoice Table].[Transaction ID],[Invoice Amount],[Enter Date]) AS Balance
   FROM [Invoice Table] INNER JOIN [Invoice Payment Table] 
   ON [Invoice Table].[Transaction ID]=[Invoice Payment Table].[Transaction ID]
   GROUP BY [Invoice Table].[Transaction ID], [Invoice Table].[Invoice Amount], [Enter Date],
    fCalcBalance([Invoice Table].[Transaction ID],[Invoice Amount],[Enter Date]);

   Public Function fCalcBalance(lngTransID As Long, curInvoiceAmt As Currency, dteDate As Date) As Currency
     fCalcBalance = curInvoiceAmt - DSum("[Payment Amount]", "[Invoice Payment Table]", "[Payment Date] < #" & _
                                    dteDate & "# AND [Transaction ID] = " & lngTransID)
   End Function

[Rabbit]

Something like this

SELECT InvoiceNumber,
   (InvoiceAmount - 
      (SELECT SUM(Payments) FROM PaymentsTable
       WHERE PaymentDate <= #3/31/2011# AND InvoiceNumber = T.InvoiceNumber)
   ) AS Balace
FROM TableInvoices AS T

[Marsha McCarthy]

Thanks a million!!!!!! I am going to try it out now

[ADezii]

@Rabbit - Is there any way in which you can Parameterize the Date inside the Sub-Query, instead of Hard-Coding it?

[Rabbit]

Yeah, you can do it the same way you would for a regular query. I'm just responding from a phone so it's easier to type shorter messages without scrolling back and forth to look at the original message.