access db carry forward balance vs. table design change?

[jason boyer]

with respect to this post.
http://www.eggheadcafe.com/software/aspnet/31309039/updating-several-records.aspx

i am working on a database to use in a small town for home water usage tracking and billing. the db is structured with a customer info table and a monthly usage and charges table. there is a relationship setup for the home address field, one to many. kinda like this....
tblCustomerInfo:.....
fName
lName
address
phone
tblMonthlyReadings:....
address
readMonth(Date)
meterReading
paymentAmount
amountBilled
amountPastDue
lateFee
credit

i think what i want to do is carry forward the left over balance or credit. after reading the above post, maybe i should redesign and maybe make a table to just keep a running balance. not sure what to do? any ideas? heres my thoughts...
option 1, use update query or function to fill in tblMonthlyReadings readMonth with the next month and then amountPastDue with any leftover balance. still dont know how i can do this? tried the code on the above post with little results, always errors while calculating the leftover balance.
option 2, make another table for leftover balance for each address, that would change as the months progress. dont know how i would update this value for each customer. then again i would be left without a way to look back at historical values in case of any discrepancies.

any ideas, anybody?


Submitted via EggHeadCafe - Software Developer Portal of Choice
RSA CryptoServiceProvider in .NET -- Demystified!
http://www.eggheadcafe.com/tutorial...b-3cf1cd30d5c0/rsa-cryptoserviceprovider.aspx

 

[vanderghast]

You should not update fields: updating a field won't change the number
already printed on the bill and already sent to the address. Furthermore, if
you want to track HISTORICALLY what happened (in case of someone challenging
a fee), if your database just 'killed' any amount by updating it, your
database would look ... silly... when it will be impossible to reproduce
EACH individual amounts.


You should have a table of AmountsReceived which has fields of address,
dateOfReception, and AmountPerceived.
The amountPerceived can be negative, as example, if the check is returned by
the bank.... because in this situation, you should not DELETE a record,
since doing so, you would get lost with the historical events! So, you may
decide to add an extra field explaining the reason for the existence of each
record (which is, by default, getting the check by mail, or otherwise, I
assume). You can also append a record with a negative amount in case the
system produced an erroneous bill, as example!

It would be nice to have a table about what has been billed (printed on
paper and sent), with appropriate fields. You won't update these fields,
once they are filled, since they are your witness of what was sent, but the
system can, in principle, generate the billing amounts and their details,
which is when you append new records to this table. To generate those
amounts is a complex process, if it involves late fees, penalties, etc, but
basically, you SUM the amount perceived, since creation, and SUM the amount
due (from your readings, mainly, but other special fees too, may be READING
your previous amounts in the bills table). Don't hesitate to SUM since the
beginning! That is safe to do it so, since you only sum over 'atomic'
events, and it is generally surprisingly fast! So, basically, in a query,
you will bring your table of address (no duplicated occurrence), and all the
tables with amounts (table of readings, table of special fees, and other
tables as it fits), join all these tables to the table of address (with no
dup) over the address field. You will group on address. You will sum the
various amounts:

SUM(tableOfReadings.AmountDue) + SUM(tableOfSpecialFees) -
SUM(tableOrPerceivedAmount)

as example, can produce the new amount to be billed, for each address.

Once that formula works, use it to produce your bills (or note of credit) if
the amount is not zero (or within 1.00$, or whatever is the available
politic).

Once again, special fees amount can be + or -, since you don't delete
record, but append new ones to 'correct' mistakes, same for perceived
amount. Delete record ONLY IF you are really sure you won't need it as
potential backup.



Hoping it makes sense,
Vanderghast, Access MVP

 

[jason boyer]

thank you. did a simple update query to get values carried forward to the next month. works great, thanks again.



vanderghast wrote:

You should not update fields: updating a field will not change the
03-Dec-09

You should not update fields: updating a field will not change the number
already printed on the bill and already sent to the address. Furthermore, if
you want to track HISTORICALLY what happened (in case of someone challenging
a fee), if your database just 'killed' any amount by updating it, your
database would look ... silly... when it will be impossible to reproduce
EACH individual amounts.


You should have a table of AmountsReceived which has fields of address,
dateOfReception, and AmountPerceived.
The amountPerceived can be negative, as example, if the check is returned by
the bank.... because in this situation, you should not DELETE a record,
since doing so, you would get lost with the historical events! So, you may
decide to add an extra field explaining the reason for the existence of each
record (which is, by default, getting the check by mail, or otherwise, I
assume). You can also append a record with a negative amount in case the
system produced an erroneous bill, as example!

It would be nice to have a table about what has been billed (printed on
paper and sent), with appropriate fields. You will not update these fields,
once they are filled, since they are your witness of what was sent, but the
system can, in principle, generate the billing amounts and their details,
which is when you append new records to this table. To generate those
amounts is a complex process, if it involves late fees, penalties, etc, but
basically, you SUM the amount perceived, since creation, and SUM the amount
due (from your readings, mainly, but other special fees too, may be READING
your previous amounts in the bills table). Don't hesitate to SUM since the
beginning! That is safe to do it so, since you only sum over 'atomic'
events, and it is generally surprisingly fast! So, basically, in a query,
you will bring your table of address (no duplicated occurrence), and all the
tables with amounts (table of readings, table of special fees, and other
tables as it fits), join all these tables to the table of address (with no
dup) over the address field. You will group on address. You will sum the
various amounts:

SUM(tableOfReadings.AmountDue) + SUM(tableOfSpecialFees) -
SUM(tableOrPerceivedAmount)

as example, can produce the new amount to be billed, for each address.

Once that formula works, use it to produce your bills (or note of credit) if
the amount is not zero (or within 1.00$, or whatever is the available
politic).

Once again, special fees amount can be + or -, since you do not delete
record, but append new ones to 'correct' mistakes, same for perceived
amount. Delete record ONLY IF you are really sure you will not need it as
potential backup.



Hoping it makes sense,
Vanderghast, Access MVP

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
ASP.NET Remote Scripting, Yes! AJAX, NOT!
http://www.eggheadcafe.com/tutorial...bec-413cc4725d38/aspnet-remote-scripting.aspx