Updating a table

[MidMO]

I've got a problem I've come up with and I'll try to explain it best as
possible. I've got an application that has 4 fields.

Contract Number, Contract Amount, Import Date, Report Date

I'd like to add an additional field to this table called "Fee".

The fee is based on the "Contract Amount" and will only apply in the
month where the "Contract Amount" is Greater then Zero and not the
first month a Contract Number appears. For example if in the first
month a contract appears for you and the balance is zero, the next
month the balance is $100 but no fee is charged because the previous
month was $0, the next month the balance is $200 and a fee is charged
for the first time. Another example is that if a contract appears for
the first time and has a balance of $100, no fee is charged because
it's the first time that contract appears, the next month the balance
is $200 and a fee is charged because the previous months balance is
greater than $0.

Basically, the clients are getting the first month they have with a
balance without a fee being charged.


Anyone have any thoughts on this? If I should post in a different
Group, feel free to let me know.

Thanks,
Chris

 

[Adam Turner via AccessMonster.com]

I've got a problem I've come up with and I'll try to explain it best as
possible. I've got an application that has 4 fields.

Contract Number, Contract Amount, Import Date, Report Date

I'd like to add an additional field to this table called "Fee".

The fee is based on the "Contract Amount" and will only apply in the
month where the "Contract Amount" is Greater then Zero and not the
first month a Contract Number appears. For example if in the first
month a contract appears for you and the balance is zero, the next
month the balance is $100 but no fee is charged because the previous
month was $0, the next month the balance is $200 and a fee is charged
for the first time. Another example is that if a contract appears for
the first time and has a balance of $100, no fee is charged because
it's the first time that contract appears, the next month the balance
is $200 and a fee is charged because the previous months balance is
greater than $0.

Basically, the clients are getting the first month they have with a
balance without a fee being charged.

Anyone have any thoughts on this? If I should post in a different
Group, feel free to let me know.

Thanks,
Chris

Hi Chris,

Let me paraphrase this with some psuedocode:

If First-Time Contract = False AND Balance = 0 Then
Balance = 100
+Fee
ElseIf First-Time Contract = False AND Balance > 0 Then
Balance = 200
+Fee
ElseIf First-Time Contract = True AND Balance = 0 Then
Balance = 100
No Fee
ElseIf First-Time Contract = True AND Balance > 0 Then
Balance = 200
No Fee
End If

Is this right?

 

[MidMO]

I don't think that is quite right. Your balance only changes because
of contributions you make and those numbers I'm getting from an import.
It's a retirement plan company trying to figure the fees they are
going to charge.

Let me try to explain again.....

Let's say you enroll and get a contract number in August and the
balance in your account is $0 that month.....then in September your
contract number will appear again in the table, this months balance
will be $100 because you've contributed to the account set up the month
prior.....Then in October, your contract number will appear again in
the table, this month the balance will be $200, again because of
contributions made by you.....October will be the first month a fee
will be calculated and charged to you because the first month you have
a balance is free.

I should point out there are two tables involved in this and this is
the "many" side of a "one-to-many" relationship with the contract
number being the key and appearing many times (12 times each year).
Each months information will be appended to this table.

Any help is appreciated.

 

[Adam Turner via AccessMonster.com]

I don't think that is quite right. Your balance only changes because
of contributions you make and those numbers I'm getting from an import.
It's a retirement plan company trying to figure the fees they are
going to charge.

Let me try to explain again.....

Let's say you enroll and get a contract number in August and the
balance in your account is $0 that month.....then in September your
contract number will appear again in the table, this months balance
will be $100 because you've contributed to the account set up the month
prior.....Then in October, your contract number will appear again in
the table, this month the balance will be $200, again because of
contributions made by you.....October will be the first month a fee
will be calculated and charged to you because the first month you have
a balance is free.

I should point out there are two tables involved in this and this is
the "many" side of a "one-to-many" relationship with the contract
number being the key and appearing many times (12 times each year).
Each months information will be appended to this table.

Any help is appreciated.

OK, I think I can help you Chris but I have a couple of questions.

The balance is determined by what table & field?
The import date determines the month?

 

[MidMO]

The Balance comes from an import that happens monthly. The balance is
only held in the "many" table named (tblMonthlyBalances) and the field
name is "balance".

Yes, The import date is used to determine the month.