V
Vel.
Hello,
I have developed a program using access which bills insurance companies based on various client's insurance, activity, etc. My problem is billing secondary insurances. Here are the tables involved
tblClient: contains client informatio
tblClientIns: contains fields [ClientInsID]*, [ClientID], [InsuranceID], [CoverageID], [IsPrimary], and [Order
tblActivity: contains fields [ActivityID]*, [ClientID], [DateOf], [ProcID], [ProcCharge], [InsCharge], [ClientCharge],
[ClientPay], [InsuranceID
tblPayments: contains fields [PaymentID]*, [ActivityID], [InsuranceID], [Payment], [Adjustment], [Reasoning], [DateOf
tblInsurance: contains insurance informatio
all tables also contain the fields [EnteredOn] and [EnteredBy
I need to set up a query which will find out which insurance should currently be billed. I need to do this by checking the table tblPayments to first see if the sum of the payments and adjustments in that table are enough to cover the InsCharge field in the activity table. If so, I need to omit those records from the query as the activity has been paid in full
Next I need to find out which insurance should be billed (this is the part that I'm really having alot of trouble with). To do this there needs to be an entry in the payments table for each insurance, in order by the field [Order] in tblClientIns. There may even be a payment of $0.00 entered, indicating a rejection, but there will still be an entry to pick up, I just can't figure out how to then move onto the next insurance company
Example
John Doe has a checkup on 9-5-03 which costs $50.00 and a surgery on 9-11-03 which costs $120.00. We bill the insurance tagged as [IsPrimary]=True. We get a payment voucher of $25 for 9-5 and $60.00 for 9-11. These payments are entered into tblPayment. The system should then automatically bill the secondary insurance (or Order=2 insurance) by seeing that the last insurance that paid was the first one (or Order=1). When printing the activity on the claim form, it would also pull the amount paid previously, so as to not overbill secondary insurance.
So for the example above, the output would include a main report which pulls the information for that client and that insurance as well as generating a subform similar to the following
DateOf ProcID Charge Paid Balanc
-------------------------------------------------
9-5-03 CKUP $50.00 $25.00 $25.0
9-11-03 CKUP $120.00 $60.00 $60.0
Total: $85.0
I know it's complicated, and I'd be happy to clarify if necessary on this board
Thanks
Vel
I have developed a program using access which bills insurance companies based on various client's insurance, activity, etc. My problem is billing secondary insurances. Here are the tables involved
tblClient: contains client informatio
tblClientIns: contains fields [ClientInsID]*, [ClientID], [InsuranceID], [CoverageID], [IsPrimary], and [Order
tblActivity: contains fields [ActivityID]*, [ClientID], [DateOf], [ProcID], [ProcCharge], [InsCharge], [ClientCharge],
[ClientPay], [InsuranceID
tblPayments: contains fields [PaymentID]*, [ActivityID], [InsuranceID], [Payment], [Adjustment], [Reasoning], [DateOf
tblInsurance: contains insurance informatio
all tables also contain the fields [EnteredOn] and [EnteredBy
I need to set up a query which will find out which insurance should currently be billed. I need to do this by checking the table tblPayments to first see if the sum of the payments and adjustments in that table are enough to cover the InsCharge field in the activity table. If so, I need to omit those records from the query as the activity has been paid in full
Next I need to find out which insurance should be billed (this is the part that I'm really having alot of trouble with). To do this there needs to be an entry in the payments table for each insurance, in order by the field [Order] in tblClientIns. There may even be a payment of $0.00 entered, indicating a rejection, but there will still be an entry to pick up, I just can't figure out how to then move onto the next insurance company
Example
John Doe has a checkup on 9-5-03 which costs $50.00 and a surgery on 9-11-03 which costs $120.00. We bill the insurance tagged as [IsPrimary]=True. We get a payment voucher of $25 for 9-5 and $60.00 for 9-11. These payments are entered into tblPayment. The system should then automatically bill the secondary insurance (or Order=2 insurance) by seeing that the last insurance that paid was the first one (or Order=1). When printing the activity on the claim form, it would also pull the amount paid previously, so as to not overbill secondary insurance.
So for the example above, the output would include a main report which pulls the information for that client and that insurance as well as generating a subform similar to the following
DateOf ProcID Charge Paid Balanc
-------------------------------------------------
9-5-03 CKUP $50.00 $25.00 $25.0
9-11-03 CKUP $120.00 $60.00 $60.0
Total: $85.0
I know it's complicated, and I'd be happy to clarify if necessary on this board
Thanks
Vel