Update Field based on other form

[Sheila.deJesus]

I have two tables:

Table1 - show details of customer payment (full payment / with credit terms up to a maximum of six (6) months)

ColID - (PK/Autonumber)
Col1
Col2
Col3
Col4
Col5
Col6


Table2 - shows insurance agent's prorata commission (based on cleared payment).
ComID - (PK/Autonumber)
ColID
Com1
Com2
Com3
Com4
Com5
Com6

Scenario: If a customer was given 6-month-credit term to pay for his insurance premium, then the insurance agent's commission will also be given in six monthly terms.

Example Computation:
Amount of premium is 12,000 (payable in 6 months = 2,000/monthly)
Agents Commission is 10% of Premium (1,200 = 200/monthly)

What i need is a code to automatically update Table2 if Table1 is updated. If the dbase user updates Col2 based on the amount the customer pays, the field Com2 must also be updated.

I hope this is feasible.

Thanks!


Sheila

 

[CEH]

Sheila, First, Yes, It's feasible........ BUT................
Look at your schema..... Your tables... your structure. You REALLY need more then 2 tables to make it work properly... For example... I see tables like... tblCustomers, tblAgents, tblCommisionRates, tblPolicyLength, and tblPolicySale. Some tables very simple... ie... "tblCommisionRate", just 2 fields.. RateID and Rate. All tables would tie together with the "PolicySale" table......
Set that up and see if things work better.

 

[Sheila.deJesus]

Hi Curtis!

Thanks for your inputs. I actually have the tables you've stated. I just mentioned the two tables just so to make it simplier to explain. I guess, i didn't explain the scenario well.

I am so confused with how to go about my project. I'm a novice and i'm not educated well on computers. I'm just using logic and common sense, and of course.. getting assistance from people like you who are very willing to share their knowledge.

Col1 to Col6 represents deferred payments of the customer. Col meaning Collection. And Com1 to Com6 represents Sales Agent's Commission. As i've mentioned, Sales Agents can only get their respective Commission for every cleared payment.

I really can't figure out how to go about it..

Let me tabulate the scenario:

1. Total Premium of customer amounts to 12,000 and the customer is given 6-month credit terms.
2. Agent's commission is 10% of the Total Premium, so that would be 1,200 (divided by the number of months/terms given to customer)

I have the following fields on Table1 (CollectionData)

ColID - PK/Autonumber
Col1 - Initial Payment
Date1 - Date of 1st Premium Payment
OR1 - Official Receipt
...... and so on

On my Table2 (CommissionData), i have the following fields:

ComID - PK/Autonumber
ColID
Com1 - Sales Agent's Prorata Commission for the Initial Payment
Com2 - Sales Agent's Prorata Commission for the 2nd Payment
Com3 - Sales Agent's Prorata Commission for the 3rd Payment.... etc..

I would want Com2 to automatically update once database user enter amount collected in Col2... and so on...

Thanks really for your inputs!

Sheila

 

[CEH]

I think I would look into getting rid of the Com1,2,3,4,5,6.... I would have a Commissions Payable, payment Date, payment amount, payment number. You could tie your commissions payable by PolicyID and AgentID. My views may differ from some on this but my background is accounting. You also have a field for "Initial Payment"..... But what about total payment recievable? And also think about "Cash" or "Accrual" basis.
Just a few things for you to ponder

 

[Sheila.deJesus]

Thanks Curtis! I'm beginning to get the idea now.