Re: Really bad table design...
- From: "Tom Ellison" <tellison@xxxxxxxxxxx>
- Date: Tue, 17 Jan 2006 16:18:54 -0600
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!
>
>
.
- Follow-Ups:
- Re: Really bad table design...
- From: Stephanie
- Re: Really bad table design...
- Prev by Date: Re: Splitting and archiving a large database
- Next by Date: Re: person in multiple departments
- Previous by thread: Re: Can I change the way that a record is placed in a subform?
- Next by thread: Re: Really bad table design...
- Index(es):
Relevant Pages
|
