I’m having trouble figuring out how to create installment records for future months. I’m hoping someone in the forum can help. Thanks in advance.
Some history: I’m trying to convert a very complicated Excel process into Access for better tracking. The database will invoice commissions due from outside sales. We don’t administer the contract but need to track what commission is due this month and for future periods. Commission is 10% of the contract down payment or if the contract is not paid in full when signed the commission is 10% of each month’s installments.
I have a table called “PaticipantContract” with many fields but the most important are the following with some data:
Contract 1
PaticipantContractID=1
TotalFee=$7990
DownPayment=$7290
Discount=$700
Balance=$7290 (Calculated [TotalFees]-[DownPayment]-[Discount])
TermPeriod=0
TermAmount=$0
TermFirst PaymentDate=null
ContractDate: 07/10/2011
PaticipantContractStatusID=4 (defaults when record is created “Process Planned Payments”)
PIF=True
Contract 2
PaticipantContractID=2
TotalFee=$7990
DownPayment=$1995
Discount=$0
Balance=$5995 (Calculated [TotalFees]-[DownPayment]-[Discount])
TermPeriod=5
TermAmount=$1199
TermFirst PaymentDate=8/15/2011
ContractDate: 07/15/2011
PaticipantContractStatusID=4 (defaults when record is created “Process Planned Payments”)
PIF=False
I want to take this information and create records in a table called “PlannedPayments” for payments/commission due this month and for future periods. I will use this table to invoice each month based on PlannedPaymentDate. The fields in that table are:
PlannedPaymentID , primary key
PaticipantContractID, foreign key
PlannedPaymentDate, date
PlannedPaymentAmount, currency
PlannedCommission, calculated = PlannedPaymentAmoun * 10%
The goal would be to create the commission’s records in the “PlannedPayments” table for both the down payment and the future installments. When then payments are created the PaticipantContractStatusID in the “PaticipantContract” would be updated to “5” (Planned Payments Processed) and the “PlannedPayments” table should look like the below. The two first two records are the down payments for both contracts and remaining are the installments for contract 2. My invoice for July in this example would be include records 1 and 2.
1 1 7/10/2011 $7,290.00 $729.00
2 2 7/15/2011 $1,995.00 $199.50
3 2 8/15/2011 $1,199.00 $119.00
4 2 9/15/2011 $1,199.00 $119.00
5 2 10/15/2011 $1,199.00 $119.00
6 2 11/15/2011 $1,199.00 $119.00
7 2 12/15/2011 $1,199.00 $119.00
Here are my steps which I would like to stitch together into a macro and run after contracts are added to the db.
1) First find records with down payment commissions and append to “PlannedPayments”. I created a query qry_ProcessPlannedPayments_DownPayment that reads:
INSERT INTO PlannedPayment ( PaticipantContractID, PlannedPaymentAmount, PlannedPaymentDate )
SELECT PaticipantContract.PaticipantContractID, PaticipantContract.DownPayment, PaticipantContract.ContractDate
FROM PaticipantContract
WHERE (((PaticipantContract.PaticipantContractStatusID)=4));
2) Next update PIF records in the “PaticipantContract” table where PaticipantContractStatusID =4.
I created a query qry_UpdateProcessedPayments that reads:
UPDATE PaticipantContract SET PaticipantContract.PaticipantContractStatusID = 5.
WHERE (((PaticipantContract.PaticipantContractStatusID)=4) AND ((PaticipantContract.PIF)=True));
Here is where I am stuck. I can find all the records that have terms (Not PIF) but how do I in the example of a term contract create records for each future month?
thanks for your help,
Cindy
Some history: I’m trying to convert a very complicated Excel process into Access for better tracking. The database will invoice commissions due from outside sales. We don’t administer the contract but need to track what commission is due this month and for future periods. Commission is 10% of the contract down payment or if the contract is not paid in full when signed the commission is 10% of each month’s installments.
I have a table called “PaticipantContract” with many fields but the most important are the following with some data:
Contract 1
PaticipantContractID=1
TotalFee=$7990
DownPayment=$7290
Discount=$700
Balance=$7290 (Calculated [TotalFees]-[DownPayment]-[Discount])
TermPeriod=0
TermAmount=$0
TermFirst PaymentDate=null
ContractDate: 07/10/2011
PaticipantContractStatusID=4 (defaults when record is created “Process Planned Payments”)
PIF=True
Contract 2
PaticipantContractID=2
TotalFee=$7990
DownPayment=$1995
Discount=$0
Balance=$5995 (Calculated [TotalFees]-[DownPayment]-[Discount])
TermPeriod=5
TermAmount=$1199
TermFirst PaymentDate=8/15/2011
ContractDate: 07/15/2011
PaticipantContractStatusID=4 (defaults when record is created “Process Planned Payments”)
PIF=False
I want to take this information and create records in a table called “PlannedPayments” for payments/commission due this month and for future periods. I will use this table to invoice each month based on PlannedPaymentDate. The fields in that table are:
PlannedPaymentID , primary key
PaticipantContractID, foreign key
PlannedPaymentDate, date
PlannedPaymentAmount, currency
PlannedCommission, calculated = PlannedPaymentAmoun * 10%
The goal would be to create the commission’s records in the “PlannedPayments” table for both the down payment and the future installments. When then payments are created the PaticipantContractStatusID in the “PaticipantContract” would be updated to “5” (Planned Payments Processed) and the “PlannedPayments” table should look like the below. The two first two records are the down payments for both contracts and remaining are the installments for contract 2. My invoice for July in this example would be include records 1 and 2.
1 1 7/10/2011 $7,290.00 $729.00
2 2 7/15/2011 $1,995.00 $199.50
3 2 8/15/2011 $1,199.00 $119.00
4 2 9/15/2011 $1,199.00 $119.00
5 2 10/15/2011 $1,199.00 $119.00
6 2 11/15/2011 $1,199.00 $119.00
7 2 12/15/2011 $1,199.00 $119.00
Here are my steps which I would like to stitch together into a macro and run after contracts are added to the db.
1) First find records with down payment commissions and append to “PlannedPayments”. I created a query qry_ProcessPlannedPayments_DownPayment that reads:
INSERT INTO PlannedPayment ( PaticipantContractID, PlannedPaymentAmount, PlannedPaymentDate )
SELECT PaticipantContract.PaticipantContractID, PaticipantContract.DownPayment, PaticipantContract.ContractDate
FROM PaticipantContract
WHERE (((PaticipantContract.PaticipantContractStatusID)=4));
2) Next update PIF records in the “PaticipantContract” table where PaticipantContractStatusID =4.
I created a query qry_UpdateProcessedPayments that reads:
UPDATE PaticipantContract SET PaticipantContract.PaticipantContractStatusID = 5.
WHERE (((PaticipantContract.PaticipantContractStatusID)=4) AND ((PaticipantContract.PIF)=True));
Here is where I am stuck. I can find all the records that have terms (Not PIF) but how do I in the example of a term contract create records for each future month?
thanks for your help,
Cindy
