Re: Really bad table design...

Dear Stephanie:

Your post answers the question I asked, I think, but you may not have known
it. It sounds very much like you want the simpler, and more common, Balance
Forward accounting.

The statement you send out will pretty much answer this question. It says,
the balance forward (from the previous statement) is so much. Since that
statement, you have a chronological listing of charges and payments, and a
new balance at the bottom of the new statement. This balance at the bottom
of the statement is ALWAYS at the top of the next statement. Is this right?

The table structure you orginally suggested is not what I would expect.
There is no "relationship" between the charges and the payments. It is not
the case that a payment is attributed to a specific charge (dues).

I suggest you have just two tables at the heart of this: member accounts
and account activity, or just accounts and activities.

In the activities table, put all the dues, payments, credit memos, debit
memos, write-offs, service charges, etc. Put a column in this table to say
which of the activity types (what I just listed) is represented by that row.
You should have a separate, fairly static table of these activity types. I
typically put a column in that table telling whether each activity adds or
subtracts from the balance. That way, a check for $100 is just entered as
100.00, and dues for $30.00 is just entered as 30.00, but the dues add to
the balance while the payment subtracts from the balance. There will also
be a Balance Forward record in this table that delineates each monthly(?)
statement. Do not allow any changes to any rows prior to the most recent
Balance Forward row for any account. You cannot change a statement after it
has been mailed. You adjust any charges with credit/debit memos in the
current statement "month" (or whatever period applies). Do not add rows to
the table that would place that row in a previous statement period, or it
will never print on a statement.

Mistakes get made. A payment gets credited to the wrong account, and the
statements get mailed that way. In a following statement, the account that
got this payment gets debited, and the correct account credited. If you
made a service charge to the account that paid, but didn't get credited, you
reverse that charge in some following month. The mistaken charge remains on
the statement that was already sent.

Once you JOIN the tables for this in a query, it becomes easy to multiply by
the sign of the activity (+ for charges, - for credits) and sum the values.
Make a convention before you start whether the Balance Forward applies
before or after all the other activities on the same date. You cannot
readily change this policy.

You can always reprint any statement and get the same as the original (as
long as you protect against any back-posting). You can summarize all the
statements for a "year" but you must be able to define what a "year" is.
The statements will not always go out on the 30th of the month. There isn't
a 30th in February, and the 30th may fall on a weekend, or the computer
could be out of service. So, the definition of a "year" needs to be studied
and defined as well.

You can prepare a partial statement as of any time, just not recording the
balance forward.

The above are a few of the considerations in a Balance Forward accounting
system. I hope this has helped. Until you are acquainted with the
accounting as it would be done manually, you should refrain from making any
final decisions as to how it can be automated. All these features, and
their ramifications, need to be clear before you finalize a design on paper,
and that comes before making technical designs for the computer.

Tom Ellison


"Stephanie" <Stephanie@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:ECDCC712-FAC2-487D-BA68-D46D9978B571@xxxxxxxxxxxxxxxx
> Tom,
> I was so excited to have a reply! Thanks for taking the time and sounding
> so knowledgeable.
>
> Hmm... I'm not sure I totally understand the 2 options. Here's what I
> want
> to do:
> Members owe either/both Membership Dues and/or Board Dues. For Membership
> dues, members can help defray the cost by "cashing-in" their volunteer
> hours
> ("Bucks"). Caveats: 1) Board members can't defray cost of membership dues
> or
> board dues. 2) non-board members who have a family membership can pool
> their
> volunteer hours to defray membership cost. I'm good doing both 1 and 2
> manually. In addition, if someone paid membership dues and board dues
> together, I'm good with entering 2 receivables and 2 payables and
> indicating
> that one check paid for both. There are very limited instances when any
> of
> the above will happen. Manual is easier.
>
> So, I think I really have more of a combined Receivables/Payment
> situation.
> Mary and John have an outstanding A/R of $75 and they have credits of $12
> from Mary and $10 from John ("Bucks") so they still owe $53, which they
> will
> pay with real money. I thought I set it up an A/R account type table with
> the account representing membership dues and board dues, and that the
> receipt
> transactions could come from pretty much anywhere. Mary and John could
> pay
> the membership dues for their daughter Hermione. So the A/R is linked to
> Hermione, but receipt transactions can come from anywhere. Like the A/R
> folks say, we always accept cash!
>
> I wanted to put enough information on the A/R table so that I have the
> history of receipt transactions (I even thought I made it 2 tables, one
> for
> the A/R and one for the receipt transactions) so if someone says "I sent
> you
> a check a month ago" I can say "sorry, no record of it in our system. We
> show
> your payment of 6 months ago and that's it".
>
> What I don't have is an annual process that sweeps the Contacts tables and
> generates new records on the A/R table to send out invoices to Contacts
> (or a
> process to run on an individual Contact basis when a new person joins up).
>
> I would like to be able to say you owed $100 in 2005 and paid it with
> check
> #104. You owe $100 in 2006 and we don't show that we've received payment
> yet...
>
> Can it be done? Can I do it (with your help!)? Thanks for your time- I
> appreciate your efforts.
>
> "Tom Ellison" wrote:
>
>> Dear Stephanie:
>>
>> First, let's get the accounting model you want.
>>
>> There are two likely candidates: balance forward and open item. Balance
>> forward is the most common. It does not try to associate a payment with
>> a
>> debit (amount owed). It simply keeps a running total of what is owed.
>> Open
>> Item keeps track of a distribution of a payment among debits. If someone
>> owes for 3 different things, and pays for all of them with one check, you
>> have to divide the amount paid among the debits. This takes a lot more
>> work
>> for users as well as programmers. It is used much less frequently.
>>
>> There are specific ways of doing these, and they are quite different.
>> Let's
>> make sure what you want to end up having before we begin to design it,
>> OK?
>>
>> Tom Ellison
>>
>>
>> "Stephanie" <Stephanie@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:82A72133-8AE3-4B88-874A-3D5B4C585B2A@xxxxxxxxxxxxxxxx
>> > Hi. I think I have a problem with my table set up.
>> >
>> > My main form is Individuals (based on tblContacts)and it has a subform
>> > set
>> > upf or payables.
>> > The payables set up is: form called DuesLineItem (based on
>> > tblDuesLineItem)
>> > which has a subform called PaymentsLineItem (based on
>> > tblPaymentsLineItem).
>> > This allows me to have dues each year and show the pertinent payments
>> > (Dues
>> > is the one, Payments is the many).
>> >
>> > tblDuesLineItem
>> > DuesItemID
>> > TypeID (Member or Board dues)
>> > ContactID
>> > AmountDue
>> > DateCreated
>> >
>> > tblPaymentsLineItem
>> > PaymentItemID
>> > DuesItemID
>> > TypeID (2 is paying by check, 6 is "cashed-in" hours)
>> > Amount
>> > DateRemitted
>> >
>> > I cleaned out both tables so that I could see what entries are being
>> > posted
>> > and something isn't right. I have 2 Contacts that are related, paying
>> > dues
>> > together. I set up one Contact to owe $99 ($93 as a check and 6 as
>> > "cashed-in" hours), and one Contact to owe $1 (1 "cashed-in" hour). So
>> > I
>> > would expect 2 Dues entries and 3 Payments entries. Instead, here's
>> > what
>> > I
>> > have in the DuesLineItem table:
>> >
>> > DuesItemID: 15 AmountDue $99.
>> > Associated PaymentItemID: 854 TypeID: 2 Amount: $93.
>> > Associated PaymentItemID: 859 TypeID: blank (should be 6) Amount:
>> > blank
>> > (should be 6 hours)
>> >
>> > DuesItemID: 16 AmountDue $1
>> > Associated PaymentItemID: 857 TypeID: 6 Amount: 1 (1 hour)
>> >
>> > Then looking at the PaymentsLineItem table, I have a huge number of
>> > entries,
>> > rather than the three I would expect:
>> >
>> > tblPaymentsLineItem:
>> > PaymentItemID, DuesItemID, TypeID, Amount
>> > 853, blank, 6, $6
>> > 854, 15, 2, $93
>> > 855, blank, blank, blank
>> > 856, blank, blank, blank
>> > 857, 16, 6, $1
>> > 858, blank, blank, blank
>> > 859, 15, blank, blank
>> > 860-899, blank, blank, blank
>> >
>> > Oh, my! What ever have I done wrong with my database design?!
>> > I appreciate your help- Thanks!
>> >
>> >
>>
>>
>>


.

Relevant Pages

  • Re: Really bad table design...
    ... Member dues are only paid once a year based on the membership date. ... don't think I want balance forward. ... > and account activity, ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Really bad table design...
    ... there could be two membership fees at the start of every year ... The balance forward system simply lists all these, ... > dues are paid once a year based a pre-set date for all board members. ... >> and account activity, ...
    (microsoft.public.access.tablesdbdesign)
  • Re: eCheque Payment Failed
    ... It also happens if you have a PayPal balance, ... can either pay from the balance, or echeque it, but credit card options ... you had to accept the payment no matter what?. ... PayPal payment including account balance, ...
    (uk.people.consumers.ebay)
  • Re: Before I make a stink with Paypal..
    ... > On 9/10 My balance is $82.14 ... > account. ... gotten the $5.11 from your other payment option. ... out of my bank account, but I can't say for sure because I had just assumed ...
    (rec.collecting.coins)
  • Re: sorting lines on reports
    ... New Account Opened ... Down Payment ... > was opened, and on the Type O statement, a beginning balance. ... > 10/15/01 Monthly Billing 03 ...
    (microsoft.public.access.reports)
Quantcast