I've ran into a difficult debt credit balance issue

[RLehrbass]

I have volunteered to create a database for my local Chamber of Commerce, which currently does not use any electronic database for membership. I have done the easy part, I've created a very nice form to input all the member information. I require the members record to be populated in the [Members] form to process any information or payments related to a member’s record using the respective form [MemberID] field and set the default value =[Forms]![Members]![MemberID].
Now for the confusing part...

1) I need to have a way to set the members "Starting" balance by adding a debt in the [Transactions] table using the value in the [TotalDues] field in the [Members] table.
(Perhaps this can be done with a button from the [Members] form, after adding the dues information to the record. I don’t know.)
(The [Transactions] table currently has [TransactionsID](PK), [MemberID], [Date], [Debt], [Credit] and [Balance] fields.)

2) I need to click the “Payments” button on my [Members] form that opens the [Payments] form, set the value for the [PaymentAmount] field in the [Payments] form with the amount in the [Balance] field from my [Transactions] table and acAddform” to clear any other form data. Then I can adjust the amount the in the [PaymentAmount] field to accept a different value if needed. I will use an “Accept Payment” button on the [Payments] form to update the [transactions] table and close the form. (It’s more official then just closing the form with the standard close box.)



3) Finally I need to automatically add a debt “on or after” the [RenewalDate] field in the [Members] table to each members record equaling the value in the [TotalDues] field from the [Members] table for each individual members record. note: The total due and renewal date will very by member based on their agreement.
(Perhaps this calculation can be done during the “Auto Exec” startup for my database or when running a report of members with an amount in the [Balance] field of the [Transactions] table.)
(Member records are auto numbered by the [MemberID] field and is the Primary key.)

For example:
John joins the Chamber in May, his dues are $210 annually and we set his renewal date for May 1st each year. He explains his financial difficulties to us and only pays $65 today to start his membership, he will pay the remainder over a few more installment at a later date as he can afford it. (This is ok because we really need the memberships.) Next May, on his renewal date, his balence will automatically increase by $210 but on May 1St next year, he still has a balance of $25, so his new balance due is $235.
Lisa joined in April and paid the full balance, but because of the type of business she has, her renewal date is January 1st of each year, so each January her balance will go up by $450, the amount of her Total Dues. I run a monthly "Balance due" report and invoice any members with a positive balance, requesting them to make a payment.

I am confused how to write this into my db!
Could one of you please show me what this would look like or do you already have a sample I can view?.

[June7]

This is duplicate topic https://www.accessforums.net/databas...nts-52340.html

[RLehrbass]

This is duplicate topic https://www.accessforums.net/databas...nts-52340.html

I assume you did not understand my question and feel as a new user to this forum, I have placed it in the wrong thread as i am not a designer, developer or programmer. I reworded my post to better reflect my actual needs and placed my question in a general thread instead of the design thread because users with a less intricate knowledge might have a solution to share with me. I no longer have a "balance" table, now it is correctly identified as a "Transactions" table. As I require a table to track all transactions and the balance field can be a calculation.

The discussion you referred me to does not seem to relate to my question or what I need to accomplish. It sound like a method to adjust inventory quantity after an audit reveals an inaccurate count.
not a way to track a partial payment and to automatically update the running balance with an additional amount on or after a specified date.

Thank you for your consideration to my question.

[June7]

Concept is the same. Enter transactions for in/out, debit/credit, purchases/sales, obligations/payments - calculate difference of totals to get balance.

Aging of invoices (or inventory or whatever) is another matter and has been topic of many threads - finding them again is a challenge.

http://www.dbforums.com/showthread.p...vs-to-Invoices