Re: Really bad table design...

Tech-Archive recommends: Fix windows errors by optimizing your registry

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...
    ... 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: Question about a function
    ... At-Large members Meaning people all over the state and Country pay Dues One time a Year. ... they are given a grace period of another 30 days but they no long can participate in activities nor vote until paid. ... WE have different categories of membership. ... This is the true amount Collected during this "half Year". ...
    (microsoft.public.mac.office.excel)
  • Re: Really bad table design...
    ... Members owe either/both Membership Dues and/or Board Dues. ... Mary and John have an outstanding A/R of $75 and they have credits of $12 ... You owe $100 in 2006 and we don't show that we've received payment ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Really bad table design...
    ... need to associate a payment or "bucks" with a specific dues item. ... > We have family memberships so that the husband, ... > will also have the membership dues attached to their record. ...
    (microsoft.public.access.tablesdbdesign)