sql - Access union & count

Question

I'm a total beginner. I have this piece of SQL code:

SELECT Red_tag.Zglaszajacy
From Red_tag
UNION
SELECT Red_tag.Interweniujacy
From Red_tag

SELECT Count(Red_tag.Zglaszajacy) AS PoliczOfZglaszajacy, Red_tag.Interweniujacy AS PoliczOfInterweniujacy
FROM Red_tag

Union works, but I get "Syntax error of FROM". How do I get the output like this?

|Zglaszajacy&Interweniujacy|CountOfZglaszajacy|CountOfInterweniuj?cy|

EDIT Seems like its more complex than I thought. Zglaszajacy and Interweniujacy are sets of people. These two groups also overlap (about 30% of people have records in both columns). So there are cases when the same person has 3 records in Zglaszajacy and 7 entries in Interweniujacy. That is why I need to Union this two groups and then show two cumulated counts for each man. Im not sure if I explained it well, so here's a draft:

|Union of Zglaszaj?cy and Interweniujacy |Count of Zglaszajacy| Count of Interweniujacy|
---------------------------------------------------------------------------------------
|John Doe                               |     3              |  5                     |
|Tom Smith                              |  NULL              |  1                     |

Answer 1

By using your UNION query, you are taking a table that looks like this:

Zglaszajacy|Interweniujacy
A           B
C           NULL
NULL        B

And turning it into this:

Zglaszajacy
A
C
NULL
B

You have lost some of your records because UNION only keeps distinct records. Use UNION ALL instead to retain all records.

Your second SQL query was not working because you did not have the GROUP BY clause to tell the aggregation function COUNT() what it was supposed to be counting.

What I have done below is taken your UNION query and added a flag for if the record was the Zglaszajacy type or the Interweniujacy type. Then, I used that as a subquery in the FROM clause. From there, you select your person name and use the COUNT() aggregation function (or SUM() would work in this case too because I flagged it with a number) to get your totals.

Last, I added the WHERE clause to eliminate your single NULL name that is left after grouping which I am assuming would not be useful to you.

SELECT Person_Name, COUNT(Z_Type) AS Zglaszajacy_Count, 
    COUNT(I_Type) AS Interweniujacy_Count
FROM (SELECT Zglaszajacy as Person_Name, 1 AS Z_Type, NULL AS I_Type FROM Red_tag
      UNION ALL
      SELECT Interweniujacy, NULL, 1 FROM Red_tag)
WHERE Person_Name IS NOT NULL
GROUP BY Person_Name
ORDER BY Person_Name;

I tested this in MS Access and it works.