Re: Really bad table design...

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" <Stephanie@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:21645938-D192-44B6-B694-3590B9934078@xxxxxxxxxxxxxxxx
> 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" wrote:
>
>> 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...
    ... It sounds very much like you want the simpler, and more common, Balance ... the case that a payment is attributed to a specific charge. ... In the activities table, put all the dues, payments, credit memos, debit ... Balance Forward row for any account. ...
    (microsoft.public.access.tablesdbdesign)
  • 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)
  • Interest rate offer "bait and switch"?
    ... I took advantage and transferred 5k to the account ... which was at a zero balance. ... started all over at 5k minus the charges etc but it didn't add up or balance ... Capital One Credit Card Offer ...
    (misc.legal)
  • Re: Egg Money
    ... In other words, if it *is* a single account, I would not really ... appeared in the -ve balance column with nothing in the +ve. ... another purchase *and* paid some money in. ... "Egg Money customers will get up to 50 days' interest-free credit on ...
    (uk.finance)
  • Re: Egg Money
    ... My wife has recently opened an Egg Money account - which I understood to ... 50-odd days interest free credit on credit card purchases. ... a +ve and a -ve balance. ...
    (uk.finance)