Need help with query design and possible use of IIF ad Null values

[Trogg]

Good day all,

Thanks for your help in advance! I need some advice on how I might construct a query from the following information. I have table of accounts and names, etc. that looks like this:

AcctNum Name
234567 Joe Cool
567891 Jane Doe

I am going to join it to an Insurance table that contains all the insurances and the order of those insurances applicable to the account. The order of the insurance is by number with "1" being primary insurance, "2" secondary, etc. I want to use a left join on the two tables so I get all the records from the account table so that those with no insurance are shown as well. This would look like the following if nothing else was needed:

AcctNum Name Fin Class Insurance InsOrder TotalCharge
234567 Joe Cool MDCR MDCR.A 1 1300
234567 Joe Cool MDCR MDCD.SC 2 1300
234567 Joe Cool MDCR BC.SC 3 1300
567891 Jane Doe MG.CASE BC.PPO 1 250
567891 Jane Doe MG.CASE MCCR.A 2 250
345678 Bill Jones SP.AP 1450

I would like the query to pull the record set so that one record per account displays as in the following:

AcctNum Name Fin Class PriIns SecIns OtherIns TotalCharge
234567 Joe Cool MDCR MDCR.A MDCD.SC BC.SC 1300
567891 Jane Doe MG.CASE BC.PPO MCCR.A 250
345678 Bill Jones SP.AP 1450

I understand that in order to get this output I will need to drop the insurance table into the designer 3 times in order to get the insurances in there horizontally instead of vertically... my problem is how to do the criteria so I don't filter out records without insurance based on criteria (ie. Insorder = 1... 2.. etc.) and how to get the null values to show where there are no secondary or other insurances as above. Do I use IIF function somehow in all this? I hope this is clear... and thanks again in advance for your help!

 

[PHV]

What is your actual SQL code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Trogg]

Hi PHV.. and thanks for looking.

Here is the SQL for what I have that isn't working right... it obviously doesn't display records with null values where there is no secondary or tertiary insurances and I can see why hence my question about how to get those to show. It also eliminates any records from the account table that have no insurance or are self pay. I need to see those as well. Hope I'm helping...

SELECT TSG_PatientInformation.AccountNumber, TSG_PatientInformation.Name, TSG_PatientInformation.FinancialClass, TSG_BarInsurances.InsuranceBARPAT AS PrimInscode, MisIns.Name AS PrimInsName, TSG_BarInsurances.InsuredPolicyNumber AS PrimInsPolNum, TSG_BarInsurances_1.InsuranceBARPAT AS SecInsCode, MisIns_1.Name AS SecInsname, TSG_BarInsurances_1.InsuredPolicyNumber AS SecInsPolnum, TSG_BarInsurances_2.InsuranceBARPAT AS TertiaryInsCode, MisIns_2.Name AS TertiaryInsName, TSG_BarInsurances_2.InsuredPolicyNumber AS TertiaryInsPolNum, TSG_BarAccountData.TotalCharge
FROM ((((((TSG_PatientInformation LEFT JOIN TSG_BarAccountData ON TSG_PatientInformation.UrnABS = TSG_BarAccountData.UrnABS) LEFT JOIN TSG_BarInsurances ON TSG_PatientInformation.UrnABS = TSG_BarInsurances.UrnABS) LEFT JOIN TSG_BarInsurances AS TSG_BarInsurances_1 ON TSG_PatientInformation.UrnABS = TSG_BarInsurances_1.UrnABS) LEFT JOIN TSG_BarInsurances AS TSG_BarInsurances_2 ON TSG_PatientInformation.UrnABS = TSG_BarInsurances_2.UrnABS) LEFT JOIN MisIns ON TSG_BarInsurances.InsuranceBARPAT = MisIns.MnemonicMISINSDICT) LEFT JOIN MisIns AS MisIns_1 ON TSG_BarInsurances_1.InsuranceBARPAT = MisIns_1.MnemonicMISINSDICT) LEFT JOIN MisIns AS MisIns_2 ON TSG_BarInsurances_2.InsuranceBARPAT = MisIns_2.MnemonicMISINSDICT
WHERE (((TSG_BarInsurances.InsuranceOrderBARPAT)="1") AND ((TSG_BarInsurances_1.InsuranceOrderBARPAT)="2") AND ((TSG_BarInsurances_2.InsuranceOrderBARPAT)="3"));

 

[PHV]

I'd try to replace this:
WHERE (((TSG_BarInsurances.InsuranceOrderBARPAT)="1") AND ((TSG_BarInsurances_1.InsuranceOrderBARPAT)="2") AND ((TSG_BarInsurances_2.InsuranceOrderBARPAT)="3"))
with this:
WHERE Nz(TSG_BarInsurances.InsuranceOrderBARPAT,"1")="1" AND Nz(TSG_BarInsurances_1.InsuranceOrderBARPAT,"2")="2" AND Nz(TSG_BarInsurances_2.InsuranceOrderBARPAT,"3")="3"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Trogg]

I think we made some progress... replacing the where clause with yours now gets me all the records back that have no insurances at all from my accounts table. However as far as records with insurances go I am only getting records that have primary, secondary, and tertiary insurances. I'm not getting any records that have only 1 or 2 insurances. How do we get those included?