My Access 2010 database manages my family's health claims to make sure we're maximizing our benefits. After a few rounds of re-work and some research on normalization, I believe my table design is sound. I was able to create the forms and sub-forms, and the queries behind them, to enter all the types of data, by using the advice I found on ms-access-tips.blogspot.com for many-to-many forms. Now, I'm actually trying to add up the claim dollar amounts to reconcile to the bank statements... and my query shows me nothing, not even a blank line to insert a new record!
Please see the table relationship pictures in the attached zipped folder for the queries I mention below.
I was able to create a working query to map the BankDeposit/FSAClaim junction table to the Amount Insurance Paid in each FSA Claim. (The Providers table is just there to supply the text name in place of the Provider ID number.) When I ran into my later problems, I realized this one worked because the Amount Insurance Paid per claim is in the FSA Claim record (AmtPaid field).
I was able to create a working query to sum how much We Paid on each medical procedure that was applied to the Insurance Deductible/OutOfPocket Accounts for each Patient (aka family member). It adds up a field in all the medical procedures for each insurance claim assigned to a specific patient. Now, isn't that practically the same as...
My main problem: I want to map the BankDeposit/InsuranceClaim junction table to the Amount Insurance Paid in each claim. The amount insurance paid in each claim comes from adding up the InsPaid and HRAPaid fields in the Medical Procedures associated with each claim.
I was able to create a working query to sum the Amount Insurance Paid for each Medical Claim. It includes a MedInsPaid: [InsPaid] + [HRAPaid] field. And I was able to create a working query to link the BankDeposit/InsuranceClaim junction table to the Insurance Claims, including accessing fields in the Providers table. But whenever I put both the BankDeposit/InsuranceClaim junction table and the Medical Procedures table into the same query, the "new record" line disappears from the query's datasheet view!! Is it something about the complex Primary Keys??
I studied the FROM Statements in the SQL that Access generated, looking for a clue:
"Deposits to Amount Insurance Paid Per FSA Claim" SQL From Statement:
Providers INNER JOIN (FSAClaims INNER JOIN [Linkage-Deposits/FSAClaims] ON FSAClaims.ID = [Linkage-Deposits/FSAClaims].FSAClaimID) ON Providers.ID = FSAClaims.ProviderID
"Amount We Paid Per Insurance Deductible/OutOfPocket Account Per Patient" SQL From Statement:
Patients INNER JOIN (InsClaims INNER JOIN [InsProcedures-Medical] ON InsClaims.ID = [InsProcedures-Medical].InsClaimID) ON Patients.ShortName = InsClaims.Patient
"Deposits to Insurance Claims" SQL From Statement:
Providers INNER JOIN (InsClaims INNER JOIN [Linkage-Deposits/InsClaims] ON InsClaims.ID = [Linkage-Deposits/InsClaims].InsClaimID) ON Providers.ID = InsClaims.ProviderID
"Amount Insurance Paid Per Medical Claim" SQL From Statement:
(Providers INNER JOIN InsClaims ON Providers.ID = InsClaims.ProviderID) INNER JOIN [InsProcedures-Medical] ON InsClaims.ID = [InsProcedures-Medical].InsClaimID
"Deposits to Amount Insurance Paid Per Medical Claim" SQL From Statement: (NOT WORKING)
(Providers INNER JOIN (InsClaims INNER JOIN [Linkage-Deposits/InsClaims] ON InsClaims.ID = [Linkage-Deposits/InsClaims].InsClaimID) ON Providers.ID = InsClaims.ProviderID) INNER JOIN [InsProcedures-Medical] ON InsClaims.ID = [InsProcedures-Medical].InsClaimID
I tried all 5 permutations of this last FROM statement that I thought might work, but none of them got me the "new record" row back.
I tried changing up the join type between the junction table and the Insurance Claims table... LEFT, I still had nothing. RIGHT, I could see the InsClaims data displayed, but still couldn't enter a new record in the query.
In some configurations, the Access help message told me it couldn't resolve the order of some outer joins and suggested I break it into 2 queries to make the order explicit. I tried using the AmountInsurancePaidPerMedicalClaim query in a query with the Deposit/InsClaim junction, but it worked no better.
Facts to Note:
1. The InsuranceClaims table contains both Dental and Medical claims. These are separated by the ClaimType field.
2. There are currently no entries in the Deposits/InsClaims junction table. I want this query to be able to create my subform to start matching up the Deposits and InsClaims!! There aren't any entries in the Deposits/FSAClaims junction table either, but there is a blank row to enter a new record and this query works in its subform.
My back-up plan is to add a calculated/query-based? field to InsuranceClaims that provides a total for various fields across all the Medical/Dental Procedures for that claim. AND/OR I've resigned myself to removing the Deposit/InsClaim junction table and adding a DepositID field to the InsClaims table. It'll just be empty for some of the records.
Certainly there's something simple I just don't know here??
Thanks for any help!!
Please see the table relationship pictures in the attached zipped folder for the queries I mention below.
I was able to create a working query to map the BankDeposit/FSAClaim junction table to the Amount Insurance Paid in each FSA Claim. (The Providers table is just there to supply the text name in place of the Provider ID number.) When I ran into my later problems, I realized this one worked because the Amount Insurance Paid per claim is in the FSA Claim record (AmtPaid field).
I was able to create a working query to sum how much We Paid on each medical procedure that was applied to the Insurance Deductible/OutOfPocket Accounts for each Patient (aka family member). It adds up a field in all the medical procedures for each insurance claim assigned to a specific patient. Now, isn't that practically the same as...
My main problem: I want to map the BankDeposit/InsuranceClaim junction table to the Amount Insurance Paid in each claim. The amount insurance paid in each claim comes from adding up the InsPaid and HRAPaid fields in the Medical Procedures associated with each claim.
I was able to create a working query to sum the Amount Insurance Paid for each Medical Claim. It includes a MedInsPaid: [InsPaid] + [HRAPaid] field. And I was able to create a working query to link the BankDeposit/InsuranceClaim junction table to the Insurance Claims, including accessing fields in the Providers table. But whenever I put both the BankDeposit/InsuranceClaim junction table and the Medical Procedures table into the same query, the "new record" line disappears from the query's datasheet view!! Is it something about the complex Primary Keys??
I studied the FROM Statements in the SQL that Access generated, looking for a clue:
"Deposits to Amount Insurance Paid Per FSA Claim" SQL From Statement:
Providers INNER JOIN (FSAClaims INNER JOIN [Linkage-Deposits/FSAClaims] ON FSAClaims.ID = [Linkage-Deposits/FSAClaims].FSAClaimID) ON Providers.ID = FSAClaims.ProviderID
"Amount We Paid Per Insurance Deductible/OutOfPocket Account Per Patient" SQL From Statement:
Patients INNER JOIN (InsClaims INNER JOIN [InsProcedures-Medical] ON InsClaims.ID = [InsProcedures-Medical].InsClaimID) ON Patients.ShortName = InsClaims.Patient
"Deposits to Insurance Claims" SQL From Statement:
Providers INNER JOIN (InsClaims INNER JOIN [Linkage-Deposits/InsClaims] ON InsClaims.ID = [Linkage-Deposits/InsClaims].InsClaimID) ON Providers.ID = InsClaims.ProviderID
"Amount Insurance Paid Per Medical Claim" SQL From Statement:
(Providers INNER JOIN InsClaims ON Providers.ID = InsClaims.ProviderID) INNER JOIN [InsProcedures-Medical] ON InsClaims.ID = [InsProcedures-Medical].InsClaimID
"Deposits to Amount Insurance Paid Per Medical Claim" SQL From Statement: (NOT WORKING)
(Providers INNER JOIN (InsClaims INNER JOIN [Linkage-Deposits/InsClaims] ON InsClaims.ID = [Linkage-Deposits/InsClaims].InsClaimID) ON Providers.ID = InsClaims.ProviderID) INNER JOIN [InsProcedures-Medical] ON InsClaims.ID = [InsProcedures-Medical].InsClaimID
I tried all 5 permutations of this last FROM statement that I thought might work, but none of them got me the "new record" row back.
I tried changing up the join type between the junction table and the Insurance Claims table... LEFT, I still had nothing. RIGHT, I could see the InsClaims data displayed, but still couldn't enter a new record in the query.
In some configurations, the Access help message told me it couldn't resolve the order of some outer joins and suggested I break it into 2 queries to make the order explicit. I tried using the AmountInsurancePaidPerMedicalClaim query in a query with the Deposit/InsClaim junction, but it worked no better.
Facts to Note:
1. The InsuranceClaims table contains both Dental and Medical claims. These are separated by the ClaimType field.
2. There are currently no entries in the Deposits/InsClaims junction table. I want this query to be able to create my subform to start matching up the Deposits and InsClaims!! There aren't any entries in the Deposits/FSAClaims junction table either, but there is a blank row to enter a new record and this query works in its subform.
My back-up plan is to add a calculated/query-based? field to InsuranceClaims that provides a total for various fields across all the Medical/Dental Procedures for that claim. AND/OR I've resigned myself to removing the Deposit/InsClaim junction table and adding a DepositID field to the InsClaims table. It'll just be empty for some of the records.
Certainly there's something simple I just don't know here??
Thanks for any help!!
