Saving SQL results into array for calcs

[MikeLeBen]

I have a table with checks of different worths and customers can place orders and use part of said checks to pay over time.

ex.

tblChecks:
check1-worth 50€
check2-worth 20€

tblPayments:
order1-worth 25€

I want that 25€ be subtracted first from check2 leaving it at 0, and the remaining 5 from check1 leaving it at 45.

I thought of the following routine: use an array to order the results of the SQL (something like SELECT checkID FROM tblChecks WHERE worth > 0), and said array then be used in a for loop to do the math and attain expected results.

Unfortunately, though, I wouldn't know how to code it, plus if anyone comes up with a better solution I'm looking forward to hearing from them.

 

[DJkarl]

How do you relate the records between the tables together? I.E. How do you know Checks 1 and 2 are tied to order 1?

 

[MikeLeBen]

both tabls have fkCustomerID

 

[DJkarl]

My first thought would be to try and do this all in SQL. You should be able to do a running total and get the data you're looking for. Doing the math in an array or a recordset in memory is a way to go, but in order to view the data you need to put it somewhere (table, or export it to a file) or it will be gone once the database is closed.

 

[MikeLeBen]

How would I write the instruction to look for and subtract from the check with less money on (and keep track of the fact that said amount must be subtracted both from that check AND from the "AmountDue" field in tblPayments)?

 

[DJkarl]

You would need to use sub-queries, this would give you the results and running totals in the query each time you run it.

Here is a great break-down on running totals, sub-totals, grand-totals. Of course instead of summing things together you would be subtracting an amount to get what is left.
http://www.databasejournal.com/feat...ubtotals-and-Grand-Total-Without-a-Cursor.htm

 

[MikeLeBen]

Thanks for the info, i'll give it a shot tomorrow as i get back to it!

 

[MikeLeBen]

I have been halted by the flu, but as I eventually got back to it I have now spent several hours trying to figure this out without any luck.

Would you be able to put up an example of the SQL I need to perform the subtraction successfully?