How to structure the relationships in twice removed daughter table?

[Andrew5]

Two currently separate DB's, "Client Activity" and the "Checkbook." 

 

Activity DB has "Main Table" with client#, name, address, etc. Its related daughter table has charges and payments, and dates for those. 

 

Checkbook DB is a simple flat DB where we print checks and record payments. 

 

Right now the payments are entered twice, into the Activity DB and then into the Checkbook DB. Of course I want to avoid the double entry.

 

When a payment is recorded into the "Client Activity" daughter table, how to simultaneously create a create a deposit record in the Checkbook DB?

 

Second problem: The work for the CHARGES is done by 3rd parties, so when a Charge is created, it will in many circumstances, but not all, require a check to be paid to the 3rd party for each service. One third party contractor is so frequent that we write them an aggregate check at the end of each month, so each CHARGE to a client when using that 3rd party does not require a check (or a record) for each job. 

 

Whew. I believe I can program it if someone would point me in the right direction. 

[Manjit Behera]

Hi Andrew,

 

I think you are entering payments to Checkbook and then add the same to the daughter table. This is what you are suffering from double entry. If I am right then you can follow following solution.

 

Solution-

The payments that are entering into Checkbook must have a Serial#/ID field(let say PaymentSerial#).

 

Add a new field field "PaymentID" to the daughter table.

 

When you make a payment for a client you are adding a new record into Checkbook at that time you can get the PaymentSerial# of that payment record just use that value to set into PaymentID field of daughter table. So whenever you want to see the details of payment just search the details of payment in Checkbook via PaymentID. You no need to enter details of payments done into daughter table.

 

 

 

Thanks,