minus number in DMax

[Scooterbug]

My apolgizies for misinterperting. I should know better than to try and guess what ppl think...one of my flaws

 

[gemma-the-husky]

no not really

the thing is that having databases where data depends on other rows in the same table is diametrically opposed to what access does. (or should do)

what the OP is looking for is a way to maintain an ordered list

payment 1
payment 2
payment 3
payment 4
payment 5

so that each payment stores the value from the previous payment

now this should be easy if payment 6 follows payment 5 - since you can look up the value from payment 5, to use with payment 6.

so when you enter payment 6, you need to find the previous payment no 5. so you just need a mechanism to retrieve the previous payment - which may be the highest record number on file (ie payment 5) {always ignoring irrelevant records for OTHER accounts etc}

Now, a problem arises if you now realise that you omitted to enter a payment that should have followed payment 3. So if you enter this payment in the correct sequence (no 4), you now need to shuffle the values for existing payments 4,5,6. (which is complicated and a lot of work)

alternatively, you may decide to add the new payment at the end as payment 7. But depending on how you record this, the dates may not run in sequence

Now none of this is necessarily an issue - as long as you understand what might happen, and your app can deal with it. I think this all started with a situation where the incorrect values were being looked up.

But for example, if you enter two payments for the same customer on the same date, how can you decide which should be treated as the last of these, when you come to enter another payment in the future - hence you need some precise objective way to determine this - and a sequence number may be OK for this, as long as you can be sure that is the case.

Hence an alternative (the true database way perhaps!) is to decide instead that
a) the order that payments were entered is not important (although clearly the date is)
b) and the running balance isnt.

Since the balance can be obtained by adding together whatever transactions values we require

eg
all transactions
all transactions up to a given date
all transactions of a given type

etc etc

 

[Scooterbug]

Yea, I understand about running balances and such...we have a system here that generates a balance sheet with several sources involved (Invoices, payments, Credits, etc). Using union queries, temp tables and set starting point I can generate a balance sheet in report format (via the Running Sum property).

From what I can remember from the OPs db, he had a simple form that he was using to record transactions. I totally agree that the best way to figure out the current balance is to tally up all the relavent data entries and do a calculation based on that. I've been down that road when I was storing calculations in fields, and when something changed, I had to manually go in and change numbers in tables. I've since learned my lesson on that one

 

[rick roberts]

so are you telling me that i was barking up the wrong tree right from the beginning cos as gemma -- or dave says this system really causes problems if i need to correct something in a previous record -- should i be looking at deducting totals to get the appropriate number -- im presuming this would then work better if i make corrections to previous transactions?? anothe problem ive come accross with the system im trying to apply is that i cant create blank records for the future - to fill in later - -cos that would move the last record further up the line - it does seem very fragile and full of flaws

 

[gemma-the-husky]

no, i think there are different strategies that can be adopted to suit any circumstances. Its just necessary to understand the pros and cons, and possible downside of any particular strategy.

ie - most people here would say, as a rule of thumb- NEVER store the result of a calculation. Now the amount that someone owes you is the result of a calculation - the additon of all the transactions on a customers account (invoices less payments), since it can be determined in that way , wehy would you actually store the outstanding balance

well, over time, as transaction volumes increase, doing this calculation all the while can slow a system down - so many system designers would violate this NEVER rule, and WOULD store the current outstanding balance as well. They then HAVE to include a check that it still reconciles from time to time (month ends usually). So you could take this further, as you are doing, and store the balance after each transaction - this will make it easier to retrieve some information, but will give you potential reconciliaiton and update problems.

so its just an informed choice, depending on how you expect/need to use the data