How to automatically identify updated rows on form?

[dchaff]

I have an Access 2002 form that lists the orders (by unique order id) associated with a customer. When a check is received, I want to be able to update a payments field with the orders in which the check was for. The payment might not be all of the orders, so I need to identify those rows where a payment has been applied and display the applied payments on another form so that the user can review or make any final adjustments. I cannot figure out the best way to identify the updated records for later review. I have thought about bookmarks or recordsets, but am relatively new to Access VB and not sure of the best method to try. Any advice is appreciated.

 

[danvlas]

This does not have a turn-key solution.
Practically, from what I see you have a many-to-many relationship.
One order can be paid through many checks.
One check can contain payment for many orders.

You will need a 'middle' table to link the information:

PaymentID (primary key)
OrderID
CheckID
AmountPaid

A unique index on OrderID and CheckID will ensure that you don't add the same payment twice.

Do you know from the check document what amount relates to what order?
If yes, enter the information in the middle table through a form.
If not, you have to work on a FIFO basis and there are two ways:
1. Take each order, see what is the amount due and allocate the minimum betwee AmountDue and (TotalCheck - TotalPaid)
This is to make as many complete payments as possible (the most frequent requirement)


2. Calculate the total due amount of the relevant orders. Calculate the 'payment ratio' as OrderDue/TotalDue, take the AmountPaid and allocate payment to each order based on the ratio. This is the path to take if you want advanced calculations on payment ratios, graphs and so on (I made only one application with this scenario).


Have I completely misunderstood your point?


Good luck,


Daniel Vlas
Systems Consultant

 

[dchaff]

Your suggestion has got me thinking of a way to accomplish this with another table to link information. I can put the "middle" table as you suggested and that way identify a payment id with multiple orders. Another twist is that I need to then spin through each payment id and allocate a "discount" amount to each payment (if it had one), which gets into sum type of allocation routine. I just am not sure the "best" way (there never is a best way) to do allocation routines. If someone has an example, that would be appreciated. Thanks for your idea.

 

[MichaelRed]

but you should also check with the accounting dept. in many / most businesses there are formal accounting rules regarding the application of payments to debits. the rule details can be quite complex and include specific tax, interest, accounting and customer dictated terms.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places