duplicate values in answer set

[Randy]

I have a union query. I am pulling in data from two (almost identical) SQL views in two different databases.

My MS_Access application is linked to the two views

The issue is A.GroupId. In view finance_WILPMonthEndReview the field is set to NVARCHAR255 in view iq4bisfinance_3DMonthEndReview the field is set to NCHAR255.

This is my union query.

SELECT A.Dataareaid,A.a_year, A.a_month, A.GroupID, A.dimension, A.accountnum,A.companygroupaccount,A.account_type,C.groupiddesc,D.costcelldescription, B.PL1assignment, Round([A.SumOfActual_amt],2) AS ActAmt, Round([A.SumOfBudget_amt],2) AS BgtAmt
FROM ((finance_WILPMonthEndReview AS A LEFT JOIN tblgroupid AS B ON (A.dimension = B.costcell) AND (A.GroupID = B.GroupID)) LEFT JOIN tblgroupiddesc AS C ON B.GroupID = C.GroupID) LEFT JOIN tblcostcelldesc AS D ON B.costcell = D.costcell
Where A.a_year>=2014 and A.accountnum between "5000000" and "8999999"

UNION ALL Select A.Dataareaid,A.a_year, A.a_month, A.GroupID, A.Costcell, A.accountnum,A.companygroupaccount,A.account_type,C.groupiddesc,D.costcelldescription, B.PL1assignment, Round([A.SumOfActual_amt],2) AS ActAmt, Round([A.SumOfBudget_amt],2) AS BgtAmt
FROM ((iq4bisfinance_3DMonthEndReview AS A LEFT JOIN tblgroupid AS B ON (A.costcell = B.costcell) AND (A.GroupID = B.GroupID)) LEFT JOIN tblgroupiddesc AS C ON B.GroupID = C.GroupID) LEFT JOIN tblcostcelldesc AS D ON B.costcell = D.costcell
Where A.a_year>=2014 and A.accountnum between "5000000" and "8999999";


When I link my Excel pivot table to the query I get two lines

not sure why this is happening. Any help is appreciated.

 

[vbaInet]

UNION with the ALL keyword allows duplicates. Without 'ALL' duplicate records will be eliminated.