Payment related to fee not order

[OCHS]

I have created a database to collect student fees based on the Order Entry
template. My question is....I have numerous different fees related to each
student. Is there a way when a payment is made (ex $20 payment on total
order of $50) that would show me which fee was paid for? Say for instance I
have a book fee of $10, a registration fee of $10 and a locker fee of $10,
the parent pays $15 on the total. I want to show that the book fee has been
paid in full, the registration fee still has a balance of $5, and no payment
has been made yet on the locker fee. Please advise

 

[Tim Ferguson]

I have numerous different fees related to each student. Is there a
way when a payment is made (ex $20 payment on total order of $50) that
would show me which fee was paid for?

You need to make sure that the Payments table has enough information to
relate to the appropriate tables, and that depends on how they are set
up. One example might be like this:

FeesDue
StudentNumber...foreign key references Students
PaymentFor...foreign key references ChargeableAssets AgreedAmount
(primary key (StudentNumber, PaymentFor))

Payments
PaymentDate AmountPaid PaidOnBehalfOf PaidAsPartOf (primary key
PaymentDate, PaidOnBehalfOf, PaidAsPartOf)) (foreign key
(PaidOnBehalfOf, PaidAsPartOf) ref FeesDue)

but there are many other permutations depending on your needs.

Hope that helps


Tim F

 

[Larry Daugherty]

Unless there is a particular attribution specified with the payment,
why bother? I'd think that you would sum all outstanding balances
when you send a bill and that when money comes in it simply reduces
the balance. Deciding how to apply payments such as you describe is
an administrative issue to be resolved before it should be addressed
technically.

HTH

 

[OCHS]

The reason I need this is reporting issues. I need to create a report that
will show me the total amount that was paid for each specific item. When
payment is made in bulk it does not break it down between each item. It only
reports showing the total paid on each item. Example total paid is $100 for
registration fees and yearbook. When the report runs it shows $100 for
registration and $100 for yearbook not $50 / $50.

 

[Larry Daugherty]

How the ambiguous payment will be attributed is a *business rule* that
must resolve the issue. Then you can make your program produce the
correct results. That *business rule* is a part of your application
but not a part of Access nor of any other application platform..
First state the rule in your native language. You can create
demonstrably correct solutions only when you have specified what the
solution will be.

HTH