MS Access Union between Columns

Question

I have a table containing three columns with policy # in 2016,17 & 18. There are a couple of types of rows as a result:

1. policy in force in all three years(have the same policy # in the three-column)
2. policy terminated in 2016 (only have policy # in 2016 column)
3. policy started in 2017 didn't terminate (have policy # in 2017 &18)
4. policy starts in 2018 (have policy # only in 2018)

Let's say there are 1000 rows in this table

Then I tried to use the union function(2016 union with 2017, then union with 2018) in MS access to create a single column containing all the distinct policy#. However, the resulting distinct is less than the 1000 rows in the original table, which I think they suppose to match.

Any thought on what is causing this? Or it is supposed to shrink after the union?

Many thanks!

Answer 1

The UNION operator, by its definition, removes duplicates. You can use UNION ALL to keep them all.

To get the unique list of policy numbers use:

SELECT p2016 as PolicyNo FROM MyTable
UNION
SELECT p2017 as PolicyNo FROM MyTable

But to get them all (duplicates preserved) use:

SELECT p2016 as PolicyNo FROM MyTable
UNION ALL
SELECT p2017 as PolicyNo FROM MyTable