How to Fix Union Command in Access

Question

I have Two Queries where I am using Expressions to Count when a Specific Word is selected. Both queries work as intended but I want to combine them using Union Query. The union works but when the Rep name is in both queries it lists the name twice.

When I run my Union I receive the following

Rep     OppCount    DevCount    BiddingCount
Darrell    0            0           0
Josh       40           7           156
Kyle       0            0           15
Leah       26           4           55
Mark       59           8           159
Vic        25           2           191
Darrell    0            94          0
Josh       0            51          0
Kyle       0            62          0
Leah       0            101         0
Mark       0            87          1
Vic        0            8           0

SELECT Opp_New.Rep AS Rep, "" AS FaceToFaceCount, "" AS PhoneCount, "" AS EmailCount, Sum(IIf([Opp_New]![Status]="Opportunity",1,0)) AS OppCount, Sum(IIf([Opp_New]![Status]="Developing",1,0)) AS DevCount, Sum(IIf([Opp_New]![Status]="Bidding",1,0)) AS BiddingCount, Sum(IIf([Opp_New]![Status]="At MFG for Quote",1,0)) AS AtMFGCount, Sum(IIf([Opp_New]![Status]="Did Not Bid",1,0)) AS DNBCount, Sum(IIf([Opp_New]![Status]="Bid/Quote",1,0)) AS QuotedCount, Sum(IIf([Opp_New]![Status]="Won",1,0)) AS WonCount, Sum(IIf([Opp_New]![Status]="Lost",1,0)) AS LostCount
FROM Opp_New
GROUP BY Rep

UNION ALL

SELECT Calls_New.Rep AS Rep, Sum(IIf([Calls_New]![ContactType]="Face To Face",1,0)) AS FaceToFaceCount, Sum(IIf([Calls_New]![ContactType]="Phone",1,0)) AS PhoneCount, Sum(IIf([Calls_New]![ContactType]="Email",1,0)) AS EmailCount, Sum(IIf([Calls_New]![ProjectStatus]="Opportunity",1,0)) AS OppCount, Sum(IIf([Calls_New]![ProjectStatus]="Developing",1,0)) AS DevCount, Sum(IIf([Calls_New]![ProjectStatus]="Bidding",1,0)) AS BiddingCount, Sum(IIf([Calls_New]![ProjectStatus]="At MFG for Quote",1,0)) AS AtMFGCount, Sum(IIf([Calls_New]![ProjectStatus]="Did Not Bid",1,0)) AS DNBCount, Sum(IIf([Calls_New]![ProjectStatus]="Bid/Quote",1,0)) AS QuotedCount, Sum(IIf([Calls_New]![ProjectStatus]="Won",1,0)) AS WonCount, Sum(IIf([Calls_New]![ProjectStatus]="Lost",1,0)) AS LostCount
FROM Calls_New
GROUP BY Rep

I am wanting to union like the following

Rep     OppCount    DevCount    BiddingCount
Darrell     0           94          0
Josh        40          58          156
Kyle        0           62          15
Leah        26          105         55
Mark        59          95          160
Vic         25          10          191

Erik A · Accepted Answer

It looks to me you don't want to use a UNION at all, but an INNER JOIN on rep instead.

SELECT t1.Rep, t1.OppCount + t2.OppCount As OppCount, t1.BiddingCount + t2.BiddingCount As BiddingCount
FROM (
    SELECT Opp_New.Rep AS Rep, "" AS FaceToFaceCount, "" AS PhoneCount, "" AS EmailCount, Sum(IIf([Opp_New]![Status]="Opportunity",1,0)) AS OppCount, Sum(IIf([Opp_New]![Status]="Developing",1,0)) AS DevCount, Sum(IIf([Opp_New]![Status]="Bidding",1,0)) AS BiddingCount, Sum(IIf([Opp_New]![Status]="At MFG for Quote",1,0)) AS AtMFGCount, Sum(IIf([Opp_New]![Status]="Did Not Bid",1,0)) AS DNBCount, Sum(IIf([Opp_New]![Status]="Bid/Quote",1,0)) AS QuotedCount, Sum(IIf([Opp_New]![Status]="Won",1,0)) AS WonCount, Sum(IIf([Opp_New]![Status]="Lost",1,0)) AS LostCount
    FROM Opp_New
    GROUP BY Rep
) t1
INNER JOIN (
    SELECT Calls_New.Rep AS Rep, Sum(IIf([Calls_New]![ContactType]="Face To Face",1,0)) AS FaceToFaceCount, Sum(IIf([Calls_New]![ContactType]="Phone",1,0)) AS PhoneCount, Sum(IIf([Calls_New]![ContactType]="Email",1,0)) AS EmailCount, Sum(IIf([Calls_New]![ProjectStatus]="Opportunity",1,0)) AS OppCount, Sum(IIf([Calls_New]![ProjectStatus]="Developing",1,0)) AS DevCount, Sum(IIf([Calls_New]![ProjectStatus]="Bidding",1,0)) AS BiddingCount, Sum(IIf([Calls_New]![ProjectStatus]="At MFG for Quote",1,0)) AS AtMFGCount, Sum(IIf([Calls_New]![ProjectStatus]="Did Not Bid",1,0)) AS DNBCount, Sum(IIf([Calls_New]![ProjectStatus]="Bid/Quote",1,0)) AS QuotedCount, Sum(IIf([Calls_New]![ProjectStatus]="Won",1,0)) AS WonCount, Sum(IIf([Calls_New]![ProjectStatus]="Lost",1,0)) AS LostCount
    FROM Calls_New
    GROUP BY Rep
) t2 ON t1.Rep = t2.Rep

Of course, when using the INNER JOIN, you can scrape stuf, like the empty FaceToFaceCount, PhoneCount and EmailCount from the first query, and just don't add those. Since you excluded them from your sample output I haven't included them in the outer query but it should be easy enough to adjust.

How to Fix Union Command in Access

Answers (2)

Related Questions