Solved

Union SQL not working as expected

Posted on 2014-01-09
4
437 Views
Last Modified: 2014-01-10
Hello~
I have a qery I am trying to convert to SQL code and it is not working as expected.
Here is the query that worked perfectly.

SELECT Insurance_Plans.ShortDescription, Insurance_Plans.PlanType, InsHealth.Plan, Insurance_Plans.DeductionCode, InsHealth.Coverage, Insurance_Rates.EmployerRate, Insurance_Rates.EmployeeRate, BenProgPartic.BenProgram
FROM ((Insurance_Plans INNER JOIN InsHealth ON Insurance_Plans.Plan = InsHealth.Plan) INNER JOIN Insurance_Rates ON (InsHealth.Key = Insurance_Rates.Key) AND (InsHealth.Plan = Insurance_Rates.Plan) AND (InsHealth.Coverage = Insurance_Rates.Coverage)) INNER JOIN BenProgPartic ON (Insurance_Rates.BenProgram = BenProgPartic.BenProgram) AND (InsHealth.ID = BenProgPartic.ID)
WHERE (((InsHealth.ID)='111111') AND ((InsHealth.CoverageElect)='E'));

UNION SELECT Insurance_Plans.ShortDescription, Insurance_Plans.PlanType, InsHealth.Plan, Insurance_Plans.DeductionCode, InsHealth.Coverage, Insurance_Rates.EmployerRate, Insurance_Rates.EmployeeRate, BenProgPartic.BenProgram
FROM ((Insurance_Plans INNER JOIN InsHealth ON Insurance_Plans.Plan = InsHealth.Plan) INNER JOIN Insurance_Rates ON (InsHealth.Plan = Insurance_Rates.Plan) AND (InsHealth.Coverage = Insurance_Rates.Coverage)) INNER JOIN BenProgPartic ON (Insurance_Rates.BenProgram = BenProgPartic.BenProgram) AND (InsHealth.ID = BenProgPartic.ID)
WHERE (((Insurance_Plans.PlanType)<>"10") AND ((InsHealth.ID)='111111') AND ((InsHealth.CoverageElect)='E'));


UNION SELECT BenefitPlans.ShortDesc AS ShortDescription, BenefitPlans.PlanType,bp_qryLifeBenProg.Plan, BenefitPlans.DeductionCode, Insurance_Rates.Coverage AS Coverage, IIf([Insurance_Rates]![Plan] Is Null,0,[Insurance_Rates]![EmployerRate]) AS EmployerRate, IIf([BenefitPlans]![PlanType] In ('21','2Z'),[bp_qryLifeRates]![Rate],IIf([BenefitPlans]![PlanType]='25',[Insurance_Rates]![EmployeeRate],[Insurance_Rates]![EmployeeRate])) AS EmployeeRate, bp_qryLifeBenProg.BenProgram FROM (bp_qryLifeBenProg INNER JOIN bp_qryLifeRates ON bp_qryLifeBenProg.ID = bp_qryLifeRates.ID) LEFT JOIN (BenefitPlans LEFT JOIN (Insurance_Plans LEFT JOIN Insurance_Rates ON Insurance_Plans.Plan = Insurance_Rates.Plan) ON BenefitPlans.Plan = Insurance_Plans.Plan) ON (bp_qryLifeBenProg.Plan = BenefitPlans.Plan) AND (bp_qryLifeBenProg.BenProgram = BenefitPlans.BenProgram) WHERE (((bp_qryLifeBenProg.ID) = '111111') And ((BenefitPlans.Active) = True)) GROUP BY BenefitPlans.ShortDesc, BenefitPlans.PlanType, bp_qryLifeBenProg.Plan, BenefitPlans.DeductionCode, Insurance_Rates.Coverage, IIf([Insurance_Rates]![Plan] Is Null,0,[Insurance_Rates]![EmployerRate]), IIf([BenefitPlans]![PlanType] In ('21','2Z'),[bp_qryLifeRates]![Rate],IIf([BenefitPlans]![PlanType]='25',[Insurance_Rates]![EmployeeRate],[Insurance_Rates]![EmployeeRate])), bp_qryLifeBenProg.BenProgram;

UNION SELECT Insurance_Plans.ShortDescription, FSA.PlanType, FSA.Plan, Insurance_Plans.DeductionCode, '' AS Coverage, 0 AS EmployerRate, FSA.Amount AS EmployeeRate, BenProgPartic.BenProgram FROM (FSA INNER JOIN Insurance_Plans ON FSA.Plan = Insurance_Plans.Plan) INNER JOIN BenProgPartic ON FSA.ID = BenProgPartic.ID WHERE (((FSA.ID)='111111') AND ((FSA.CoverageElect)='E'));

UNION SELECT Insurance_Plans.ShortDescription, LTD.PlanType, LTD.Plan, Insurance_Plans.DeductionCode, '' AS Coverage, 0 AS EmployerRate, LTD.EmployeeAmount AS EmployeeRate, BenProgPartic.BenProgram FROM (LTD INNER JOIN Insurance_Plans ON LTD.Plan = Insurance_Plans.Plan) INNER JOIN BenProgPartic ON LTD.ID = BenProgPartic.ID WHERE (((LTD.ID)='111111') AND ((LTD.CoverageElect)='E'));

UNION SELECT EarnCode.Description AS ShortDescription, 'Add Pay' AS PlanType, AdditionalPay.EarnCode AS Plan, 'Add Pay' AS DeductionCode, '' AS Coverage, EarnCode.Amount AS EmployerRate, 0 AS EmployeeRate, BenProgPartic.BenProgram FROM (AdditionalPay INNER JOIN EarnCode ON AdditionalPay.EarnCode = EarnCode.EarnCode) INNER JOIN BenProgPartic ON AdditionalPay.ID = BenProgPartic.ID WHERE (((AdditionalPay.ID)='111111') AND ((AdditionalPay.EndDate) Is Null));

UNION SELECT '403B Savings' AS ShortDescription, '46' AS PlanType, SavingsPlans.Plan AS Plan,'X403B'AS DeductionCode, IIf([SavingsPlans]![FlatAmount]>0,'Flat $ Amt','Pct Gross') AS Coverage,0 AS EmployerRate,IIf([SavingsPlans]![PercentofGross]>0,[SavingsPlans]![PercentofGross],[SavingsPlans]![FlatAmount]) AS EmployeeRate, BenProgPartic.BenProgram FROM SavingsPlans INNER JOIN BenProgPartic ON SavingsPlans.ID = BenProgPartic.ID WHERE (((SavingsPlans.Plan)='X403b') AND ((SavingsPlans.ID)='111111') AND ((SavingsPlans.Elect)='E'));

 

When I translate it to SQL the first selection is overwritten by the second section. Yes, I know they are almost identical. There are linking issues between insHealth.Key and insurance_rates.key so I have to use 2 separate queries to get all of the data I need from the tables. When I link them I only get one set of data and I should get 3 sets. The other 2 sets of data have blank key fields and for some reason access won't see a blank field as being = betwen the 2 tables. Any help would be appreciated..

Private Sub LoadStaffBenefits(ID As String)
    '  procedure is used to get the staff benefits
    Dim db As Database
    Dim sSQL As String
    
    sSQL = "SELECT Insurance_Plans.ShortDescription, Insurance_Plans.PlanType, "
    sSQL = sSQL + "InsHealth.Plan, Insurance_Plans.DeductionCode, InsHealth.Coverage, "
    sSQL = sSQL + "Insurance_Rates.EmployerRate, Insurance_Rates.EmployeeRate, "
    sSQL = sSQL + "BenProgPartic.BenProgram "
    sSQL = sSQL + "FROM ((Insurance_Plans INNER JOIN InsHealth ON Insurance_Plans.Plan = InsHealth.Plan) "
    sSQL = sSQL + "INNER JOIN Insurance_Rates ON (InsHealth.Key = Insurance_Rates.Key) AND (InsHealth.Plan = Insurance_Rates.Plan) AND "
    sSQL = sSQL + "(InsHealth.Coverage = Insurance_Rates.Coverage)) INNER JOIN BenProgPartic ON "
    sSQL = sSQL + "(BenProgPartic.BenProgram = Insurance_Rates.BenProgram) AND (InsHealth.ID = BenProgPartic.ID) "
    sSQL = sSQL + "WHERE (((InsHealth.ID)="
    sSQL = sSQL + "'" + ID + "'"
    sSQL = sSQL + ") AND ((InsHealth.CoverageElect)="
    sSQL = sSQL + "'" + "E" + "'"
    sSQL = sSQL + "));"
    
    
    sSQL = "UNION SELECT Insurance_Plans.ShortDescription, Insurance_Plans.PlanType, "
    sSQL = sSQL + "InsHealth.Plan, Insurance_Plans.DeductionCode, InsHealth.Coverage, "
    sSQL = sSQL + "Insurance_Rates.EmployerRate, Insurance_Rates.EmployeeRate, "
    sSQL = sSQL + "BenProgPartic.BenProgram "
    sSQL = sSQL + "FROM ((Insurance_Plans INNER JOIN InsHealth ON Insurance_Plans.Plan = InsHealth.Plan) "
    sSQL = sSQL + "INNER JOIN Insurance_Rates ON (InsHealth.Plan = Insurance_Rates.Plan) AND "
    sSQL = sSQL + "(InsHealth.Coverage = Insurance_Rates.Coverage)) INNER JOIN BenProgPartic ON "
    sSQL = sSQL + "(BenProgPartic.BenProgram = Insurance_Rates.BenProgram) AND (InsHealth.ID = BenProgPartic.ID) "
    sSQL = sSQL + "WHERE(((Insurance_Plans.PlanType)<>'10') AND (((InsHealth.ID)= "
    sSQL = sSQL + "'" + ID + "'"
    sSQL = sSQL + ") AND ((InsHealth.CoverageElect)="
    sSQL = sSQL + "'" + "E" + "'"
    sSQL = sSQL + "));"

   
'For Spouse Life insurance eff 1/1/2010, this gets the spouse life rate based on the employee's dob
'updated 10/2010 for Ben Admin
    sSQL = sSQL + "UNION SELECT BenefitPlans.ShortDesc AS ShortDescription, BenefitPlans.PlanType,"
    sSQL = sSQL + "bp_qryLifeBenProg.Plan, BenefitPlans.DeductionCode, Insurance_Rates.Coverage AS Coverage, "
    sSQL = sSQL + "IIf([Insurance_Rates]![Plan] Is Null,0,[Insurance_Rates]![EmployerRate]) AS EmployerRate, "
    sSQL = sSQL + "IIf([BenefitPlans]![PlanType] In ("
    sSQL = sSQL + "'" + "21" + "'"
    sSQL = sSQL + ","
    sSQL = sSQL + "'" + "2Z" + "'"
    sSQL = sSQL + "),[bp_qryLifeRates]![Rate],IIf([BenefitPlans]![PlanType]="
    sSQL = sSQL + "'" + "25" + "'"
    sSQL = sSQL + ",[Insurance_Rates]![EmployeeRate],[Insurance_Rates]![EmployeeRate])) "
    sSQL = sSQL + "AS EmployeeRate, bp_qryLifeBenProg.BenProgram "
    sSQL = sSQL + "FROM (bp_qryLifeBenProg INNER JOIN bp_qryLifeRates ON "
    sSQL = sSQL + "bp_qryLifeBenProg.ID = bp_qryLifeRates.ID) LEFT JOIN (BenefitPlans "
    sSQL = sSQL + "LEFT JOIN (Insurance_Plans LEFT JOIN Insurance_Rates "
    sSQL = sSQL + "ON Insurance_Plans.Plan = Insurance_Rates.Plan) "
    sSQL = sSQL + "ON BenefitPlans.Plan = Insurance_Plans.Plan) "
    sSQL = sSQL + "ON (bp_qryLifeBenProg.Plan = BenefitPlans.Plan) AND "
    sSQL = sSQL + "(bp_qryLifeBenProg.BenProgram = BenefitPlans.BenProgram) "
    sSQL = sSQL + "WHERE (((bp_qryLifeBenProg.ID) = "
    sSQL = sSQL + "'" + ID + "'"
    sSQL = sSQL + ") And ((BenefitPlans.Active) = True)) "
    sSQL = sSQL + "GROUP BY BenefitPlans.ShortDesc, BenefitPlans.PlanType, bp_qryLifeBenProg.Plan, "
    sSQL = sSQL + "BenefitPlans.DeductionCode, Insurance_Rates.Coverage, "
    sSQL = sSQL + "IIf([Insurance_Rates]![Plan] Is Null,0,[Insurance_Rates]![EmployerRate]), "
    sSQL = sSQL + "IIf([BenefitPlans]![PlanType] In ("
    sSQL = sSQL + "'" + "21" + "'"
    sSQL = sSQL + ","
    sSQL = sSQL + "'" + "2Z" + "'"
    sSQL = sSQL + "),[bp_qryLifeRates]![Rate],IIf([BenefitPlans]![PlanType]="
    sSQL = sSQL + "'" + "25" + "'"
    sSQL = sSQL + ",[Insurance_Rates]![EmployeeRate],[Insurance_Rates]![EmployeeRate])), "
    sSQL = sSQL + "bp_qryLifeBenProg.BenProgram;"


    sSQL = sSQL + "UNION SELECT "
    sSQL = sSQL + "Insurance_Plans.ShortDescription, FSA.PlanType, FSA.Plan, "
    sSQL = sSQL + "Insurance_Plans.DeductionCode, "
    sSQL = sSQL + "'" + "" + "'"
    sSQL = sSQL + " AS Coverage, "
    sSQL = sSQL + "0 AS EmployerRate, FSA.Amount AS EmployeeRate, "
    sSQL = sSQL + "BenProgPartic.BenProgram "
    sSQL = sSQL + "FROM (FSA INNER JOIN Insurance_Plans ON FSA.Plan "
    sSQL = sSQL + "= Insurance_Plans.Plan) "
    sSQL = sSQL + "INNER JOIN BenProgPartic ON FSA.ID = BenProgPartic.ID "
    sSQL = sSQL + "WHERE (((FSA.ID)="
    sSQL = sSQL + "'" + ID + "'"
    sSQL = sSQL + ") AND ((FSA.CoverageElect)="
    sSQL = sSQL + "'" + "E" + "'"
    sSQL = sSQL + "));"
    
    'Add in LTD
    sSQL = sSQL + "UNION SELECT "
    sSQL = sSQL + "Insurance_Plans.ShortDescription, LTD.PlanType, LTD.Plan, "
    sSQL = sSQL + "Insurance_Plans.DeductionCode, "
    sSQL = sSQL + "'" + "" + "'"
    sSQL = sSQL + " AS Coverage, "
    sSQL = sSQL + "0 AS EmployerRate, LTD.EmployeeAmount AS EmployeeRate, "
    sSQL = sSQL + "BenProgPartic.BenProgram "
    sSQL = sSQL + "FROM (LTD INNER JOIN Insurance_Plans ON LTD.Plan "
    sSQL = sSQL + "= Insurance_Plans.Plan) "
    sSQL = sSQL + "INNER JOIN BenProgPartic ON LTD.ID = BenProgPartic.ID "
    sSQL = sSQL + "WHERE (((LTD.ID)="
    sSQL = sSQL + "'" + ID + "'"
    sSQL = sSQL + ") AND ((LTD.CoverageElect)="
    sSQL = sSQL + "'" + "E" + "'"
    sSQL = sSQL + "));"
    
   
  
    sSQL = sSQL + "UNION SELECT "
    sSQL = sSQL + "EarnCode.Description AS ShortDescription, "
    sSQL = sSQL + "'" + "Add Pay" + "'"
    sSQL = sSQL + " AS PlanType, "
    sSQL = sSQL + "AdditionalPay.EarnCode AS Plan, "
    sSQL = sSQL + "'" + "Add Pay" + "'"
    sSQL = sSQL + " AS DeductionCode, "
    sSQL = sSQL + "'" + "" + "'"
    sSQL = sSQL + " AS Coverage, "
    sSQL = sSQL + "EarnCode.Amount AS EmployerRate, 0 AS EmployeeRate, "
    sSQL = sSQL + "BenProgPartic.BenProgram "
    sSQL = sSQL + "FROM (AdditionalPay INNER JOIN EarnCode ON "
    sSQL = sSQL + "AdditionalPay.EarnCode = EarnCode.EarnCode) "
    sSQL = sSQL + "INNER JOIN BenProgPartic ON AdditionalPay.ID = BenProgPartic.ID "
    sSQL = sSQL + "WHERE (((AdditionalPay.ID)="
    sSQL = sSQL + "'" + ID + "'"
    sSQL = sSQL + ") AND ((AdditionalPay.EndDate) Is Null));"

'Updated 1/1/2010 to include Flat $ Amt or Pct of Gross and the correct amounts
    sSQL = sSQL + "UNION SELECT "
    sSQL = sSQL + "'" + "403B Savings" + "'"
    sSQL = sSQL + " AS ShortDescription, "
    sSQL = sSQL + "'" + "46" + "'"
    sSQL = sSQL + " AS PlanType, SavingsPlans.Plan AS Plan,"
    sSQL = sSQL + "'" + "X403B" + "'"
    sSQL = sSQL + "AS DeductionCode, IIf([SavingsPlans]![FlatAmount]>0,"
    sSQL = sSQL + "'" + "Flat $ Amt" + "'"
    sSQL = sSQL + ","
    sSQL = sSQL + "'" + "Pct Gross" + "'"
    sSQL = sSQL + ") AS Coverage,"
    sSQL = sSQL + "0 AS EmployerRate,"
    sSQL = sSQL + "IIf([SavingsPlans]![PercentofGross]>0,[SavingsPlans]![PercentofGross],[SavingsPlans]![FlatAmount]) "
    sSQL = sSQL + "AS EmployeeRate, "
    sSQL = sSQL + "BenProgPartic.BenProgram "
    sSQL = sSQL + "FROM SavingsPlans INNER JOIN BenProgPartic ON SavingsPlans.ID = BenProgPartic.ID "
    sSQL = sSQL + "WHERE (((SavingsPlans.Plan)="
    sSQL = sSQL + "'" + "X403b" + "'"
    sSQL = sSQL + ") AND ((SavingsPlans.ID)="
    sSQL = sSQL + "'" + ID + "'"
    sSQL = sSQL + ") AND ((SavingsPlans.Elect)="
    sSQL = sSQL + "'" + "E" + "'"
    sSQL = sSQL + "));"
    
   
    Set db = ServerDB
    Debug.Print sSQL
    Set rsBenefits = db.OpenRecordset(sSQL)
    
    Set db = Nothing
    fBenefits = True
End Sub

Open in new window

0
Comment
Question by:dminx13
  • 2
4 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39768540
try using UNION ALL  instead of UNION
0
 
LVL 11

Expert Comment

by:John_Vidmar
ID: 39768974
Semi-colons are delimiters to separate your query into multiple SQL batches, try removing all of them, see how that works.
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 39770054
you are missing  "sSQL + " in your second query


    sSQL = sSQL + "UNION SELECT Insurance_Plans.ShortDescription, Insurance_Plans.PlanType, "
    sSQL = sSQL + "InsHealth.Plan, Insurance_Plans.DeductionCode, InsHealth.Coverage, "
    sSQL = sSQL + "Insurance_Rates.EmployerRate, Insurance_Rates.EmployeeRate, "
    sSQL = sSQL + "BenProgPartic.BenProgram "
    sSQL = sSQL + "FROM ((Insurance_Plans INNER JOIN InsHealth ON Insurance_Plans.Plan = InsHealth.Plan) "
    sSQL = sSQL + "INNER JOIN Insurance_Rates ON (InsHealth.Plan = Insurance_Rates.Plan) AND "
    sSQL = sSQL + "(InsHealth.Coverage = Insurance_Rates.Coverage)) INNER JOIN BenProgPartic ON "
    sSQL = sSQL + "(BenProgPartic.BenProgram = Insurance_Rates.BenProgram) AND (InsHealth.ID = BenProgPartic.ID) "
    sSQL = sSQL + "WHERE(((Insurance_Plans.PlanType)<>'10') AND (((InsHealth.ID)= "
    sSQL = sSQL + "'" + ID + "'"
    sSQL = sSQL + ") AND ((InsHealth.CoverageElect)="
    sSQL = sSQL + "'" + "E" + "'"
    sSQL = sSQL + "));"
0
 

Author Closing Comment

by:dminx13
ID: 39771683
WOW!!! BLIND!!!! That fixed it right up!!!
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Email Header Detail 12 55
Help Required 3 97
Why get error when delete all records on a sub-form 2 16
SQL Syntax: How to force case sensitive query? 2 30
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question