New Database

[Guest]

Hi. I'm running into a bit of a problem here. This is going to be pretty
long to explain, so please bear with me.

I have a database that contains accounts, account owners, taxes owing, and
payments. I need a lot of help creating a report that is required by our
office. The report needs to show:

Date, Payment, FeesPaid, FeesOwing, DelinquentPenalty, and Balance.

The problem I'm running into is there is a different formula to calculate
the delinquent penalty and the balance for the first payment than the other
payments. So for the first payment, the fields are:

Date,
Payment,
FeesPaid,
FeesOwing,
NoOfMonthsDateDiff("m",([DelinquentDate]),([Date])),
RedemAge: [#ofmonths]*0.015,
DelinquentPenalty: ([BaseTax]*[RedemAge])
Balance: [Total]+[DelqPenalty]+[FeesOwing]-[Payment]

For the other payments, the fields are:
Date,
Payment,
FeesPaid,
FeesOwing,
NoOfMonthsDateDiff("m",([DelinquentDate]),([Date])),
RedemAge: [#ofmonths]*0.015,
DelinquentPenalty: ([BaseTax*[RedemAge])-(Payment1, 2, 3,
etc...)-(FeesPaid1, 2, 3, etc....)
Balance: [Total]+[DelqPenalty]+[FeesOwing]-[Payment]

Do any of you have any idea on how I can do this? Also, this is a running
balance report. Each payment has to be able to show the penalties and
balance as of that date (i.e., just the payments on or before that payment,
and not add the payments after). How do I do this?

Thanks, in advance, for your help.

 

[Keith Wilby]

Hi. I'm running into a bit of a problem here. This is going to be pretty
long to explain, so please bear with me.

I have a database that contains accounts, account owners, taxes owing, and
payments. I need a lot of help creating a report that is required by our
office. The report needs to show:

Date, Payment, FeesPaid, FeesOwing, DelinquentPenalty, and Balance.

The problem I'm running into is there is a different formula to calculate
the delinquent penalty and the balance for the first payment than the
other
payments. So for the first payment, the fields are:

Date,
Payment,
FeesPaid,
FeesOwing,
NoOfMonthsDateDiff("m",([DelinquentDate]),([Date])),
RedemAge: [#ofmonths]*0.015,
DelinquentPenalty: ([BaseTax]*[RedemAge])
Balance: [Total]+[DelqPenalty]+[FeesOwing]-[Payment]

For the other payments, the fields are:
Date,
Payment,
FeesPaid,
FeesOwing,
NoOfMonthsDateDiff("m",([DelinquentDate]),([Date])),
RedemAge: [#ofmonths]*0.015,
DelinquentPenalty: ([BaseTax*[RedemAge])-(Payment1, 2, 3,
etc...)-(FeesPaid1, 2, 3, etc....)
Balance: [Total]+[DelqPenalty]+[FeesOwing]-[Payment]

Do any of you have any idea on how I can do this? Also, this is a running
balance report. Each payment has to be able to show the penalties and
balance as of that date (i.e., just the payments on or before that
payment,
and not add the payments after). How do I do this?

Thanks, in advance, for your help.

Just off the top of my head I'd say that you need to normalise your table
structure. Each entity can have one or more payments so there's a
one-to-many there. Could you share with us your table/relationship
structure?

Keith.
www.keithwilby.com