Really bad table design...

[Stephanie]

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!

 

[Tom Ellison]

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]

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]

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]

Tom,
Thanks for the reply. Only, I'm not sure this is what I want.

Member dues are only paid once a year based on the membership date. Board
dues are paid once a year based a pre-set date for all board members. So I
don't think I want balance forward. If someone didn't pay their dues, they
would no longer be a member so carrying a balance forward doesn't do anything
for us.

I do want the "activity" linked to a specific charge: Dues 2006 are $75 and
payable for Member #1 on 01/05/06. Member #1 paid this charge on 01/04/06 in
the amount of $65 and 10 "bucks". I want to know all of this information as
a package. So I think I do want a relationship between charges and payments.
Whcih is what I thought I set up, but obviously did it incorrectly.

I wanted the Member form, with a subform showing the dues and then a subform
showing the payments. Dues would be a separate record each year, with the
appropriate payments. Only my table structure isn't supporting this.

I'd appreicate your suggestions! Thanks.

 

[Tom Ellison]

Dear Stephanie:

I understand what you're saying. Please consider this.

I think you said a family may have several members. Is this all one
account? So, there could be two membership fees at the start of every year
for the husband and wife. When a child becomes old enough to join, they
join in the middle of the year. Is that the case?

So, if they write one check for the year, and if they combine "bucks" from
both the husband and wife to pay for it, how do you want that to look in the
finished database? More specifically:

Husband's membership $90.00
Wife's membership $90.00
Child's membership $60.00

Husband's bucks $25.00
Wife's bucks $15.00
Check $200.00

The balance forward system simply lists all these, adds them up (subtracting
the last 3 actually) and comes up with a balance of 0. How do you propose
this should look in your structure?

Balance forward also refers to how the statement will appear. The failure
to accomodate the likelihood of having some user make mistakes is a very
real problem. Be sure to plan for that as well.

Tom Ellison

 

[Stephanie]

Thanks for the walk through.
We have family memberships so that the husband, wife and child could all be
covered for $100 a year. There's no middle of the year considerations- all 3
would have the same year coverage and we work with the group to determine
when the coverage starts. Yes, manul intervention.

One of the issues is "bucks". We do track them per person, so that the
husband and wife could each earn "bucks" and cash them in against the joint
family membership cost. I have a flag for PrimaryMember. Each member shows
Individual bucks and the PrimaryMember (say, husband) lists both Individual
and Family bucks (a combo of both individual's bucks). This PrimaryMember
will also have the membership dues attached to their record. However, in
order to have the wife's bucks zero out, I have to manually split the family
membership cost according to bucks earned (and truly, there are only a
handful of couples that we have to jump through hoops for):

PrimaryMember (husband) owes $100 overall.
Husband has 9 bucks, wife had 3 bucks.
Leaves husband owing $88, wife owing $3.
Husband pays $88 plus 9 bucks.
Wife pays $3 using 3 bucks.

So yes, I had to split up the membership dues so that when the wife cashes
her bucks in, she doesn't end up with a negative record. A bit messy, but
there you have it! Unless I've done something wrong. Again.

In any case, I think I'll review the sample membership database- I found
some info at:
http://msdn.microsoft.com/library/d.../OfficeAccessBuildingApplicationsCh4_Book.asp

that looks more like what you're trying to explain to me. There is only 1
table that handles dues and payments so I'll see if I can understand how it
works.

Thanks!

 

[Tom Ellison]

Dear Stephanie:

Our whole discussion so far has illustrated the importance of mentally
modeling the problem before you start creating tables. This can be very
instructive to anyone getting started.

The balance forward methods I've tried to persuade you to consider should be
a good approach. I still think you will need the ability to correct
mistakes after statements are sent. I also do not see where this method
would not do what you want. The way I see your situation, there is no real
need to associate a payment or "bucks" with a specific dues item. Rather,
if there is only one dues item per account, just associating the payment
with the account is sufficient. If there ever is a situation where there is
more than one debit to an account per year, you will have the problem I
suggested. If not, then you have lost nothing.

Only you know the exact complications of the situation you have. You must
study this and make the decisions. I only hope I have presented the
alternative clearly so you can consider how it would work.

Tom Ellison

 

[Stephanie]

Tom,
Thanks for your level-headed persistence. I made the change to the balance
forward method with excellent results. I did need to rewrite and redesign a
bit but I think it was worth the effort. My tables are much better behaved
now- no random entries appearing.
Thanks for your help- I appreciate your efforts and your generousity in
sharing your knowledge. Cheers!

 

[Tom Ellison]

Dear Stephanie:

I'm so glad to hear this. I felt initially that you needed accounting
advise as much as computer advise. I hardly think of myself as an
accountant, but it certainly becomes part of the technical side, and in a
big way.

I hope and pray I have not given you and advice you'll regret. What I said
is hard won over a period of (gulp) 25 years, and has served me well.

One final advice. You weren't easy to convince, and that's fine. But you
did listen, absorb, and finally see some reason to my madness. Don't lose
that! It's all good, and will stand you well in the long run.

Tom Ellison