Apply Multiple Payments

[Ira]

I have a design and implementation issue that I need some help with.

I have a table of orders each with a unique OrderID. The total of each
order is derived on the fly by adding the contents of each order
detail line. This is working fine.
Now I am trying to finalize the payments table and how to implement
the application of multiple small payment toward an order total. I
created a payments table with a ClientID and OrderID as well as
PaymentDate and PaymentAmount. I am having trouble calculating and
displaying the order balance after apply multiple small payments and a
way to display this using a continuous form with a grid look so I can
display multiple orders each on a line with the balance of each order.

Can anyone give me some suggestions?

Thanks,
Ira

 

[Tony Toews]

I have a table of orders each with a unique OrderID. The total of each
order is derived on the fly by adding the contents of each order
detail line. This is working fine.
Now I am trying to finalize the payments table and how to implement
the application of multiple small payment toward an order total. I
created a payments table with a ClientID and OrderID as well as
PaymentDate and PaymentAmount. I am having trouble calculating and
displaying the order balance after apply multiple small payments and a
way to display this using a continuous form with a grid look so I can
display multiple orders each on a line with the balance of each order.

There are others who are much better with SQL than I am.

But what I did in a similar case was to create another "sub" query
which summed the payments transactions by OrderID. Then from the
query which is the recordsource of the continuous form do a join to
the previously mentioned subquery.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/

 

[Ken Snell]

Here's an example of what Tony suggests, but as a nested subquery instead of
a separate query that is joined in the query. The below SQL statement
assumes that you ave a unique key field (a counter, like an autonumber) that
increases for each record added to the table; I'm referring to this field as
UniqueKeyField in the below SQL statement.

SELECT D.ClientID, D.OrderID, D.PaymentDate, D.PaymentAmount,
(SELECT Sum(T.PaymentAmount) AS Balance
FROM OrderTableName AS T
WHERE T.ClientID = D.ClientID AND T.OrderID = D.OrderID AND
T.PaymentDate <= D.PaymentDate AND
T.UniqueKeyField <= D.UniqueKeyField)
AS CurrentBalance
FROM OrderTableName AS D
ORDER BY D.ClientID, D.OrderID, D.PaymentDate, D.UniqueKeyField