Trouble with SQL UNION operation

Question

I have the following table:

I am trying to create an SQL query that returns a table that returns three fields: Year (ActionDate), Count of Built (actiontype = 12), Count of Lost (actiontype = a few different ones) Bascially, ActionType is a lookup code. So, I'd get back something like:

       YEAR   CountofBuilt  CountofLost
        1905        30          18
        1929        12          99
        1940        60           1 
etc....

I figured this would take two SELECT statements put together with a UNION. I tried the following below but it only spits back two columns (year and countbuilt). My countLost field doesn't appear

My sql currently (MS Access):

SELECT tblHist.ActionDate, Count(tblHist.ActionDate) as countBuilt
FROM ...
WHERE ((tblHist.ActionType)=12)
GROUP BY tblHist.ActionDate
UNION
SELECT tblHist.ActionDate, Count(tblHist.ActionDate) as countLost
FROM ...
WHERE (((tblHist.ActionType)<>2) AND
((tblHist.ActionType)<>3))
GROUP BY tblHist.ActionDate;

Answer 1

You should not use UNION for such queries. There are many ways to do what you want, for example Updated to fit access syntax

SELECT tblHist.ActionDate,
COUNT(SWITCH(tblHist.ActionType = 12,1)) as countBuilt,
COUNT(SWITCH(tblHist.ActionType <>1 OR tblHist.ActionType <>2 OR ...,1)) as countLost
FROM ..
WHERE ....
GROUP BY tblHist.ActionDate

Answer 2

Use:

  SELECT h.actiondate,
         SUM(IIF(h.actiontype = 12, 1, 0)) AS numBuilt,
         SUM(IIF(h.actiontype NOT IN (2,3), 1, 0)) AS numLost
    FROM tblHist h
GROUP BY h.actiondate