• simflex

Member

250 Points

1129 Posts

CASE STATEMENT Complications

Greetings experts,

I have a little complicated mathematical operations I am trying to do in SQL.

I have four field names, field1, field2, field3, field4.

Field1 contains a set amount, say \$100.00

By default, field2, field3 and field4 have 0 amount

If field1(\$100.00) is greater than field2, put the difference in field3.

Example 100-50 = 50. Difference is 50 and it goes to field3. This means that the user still has \$50.00 to pay.

100-100-0. 0 goes to field4 which means user is not owning any more money.

Field1 holds the original amount to be paid.

Field2 shows how much paid (or not paid)

Field3 shows how much still being owed.

Field4 shows 0 as amount paid up.

Sample:

`Field1                 Field2                    Field3                   Field4`

`\$100.00                \$50.00                   (\$50.00)                    -`

`\$100.00                \$100.00                  (\$0.00)                    \$0.00`

Am I approaching this the right way?