A couple SQL UNION query examples.
SELECT
[%$##@_Alias].ServiceProvider,
Count([%$##@_Alias].ServiceProvider) AS SPCount
FROM
[SELECT
tblSurveyResults.[Service Providers 1] AS ServiceProvider
FROM tblSurveyResults
UNION ALL
SELECT
tblSurveyResults.[Service Providers 2] AS ServiceProvider
FROM tblSurveyResults
UNION ALL
SELECT tblSurveyResults.[Service Providers 3] AS ServiceProvider
FROM tblSurveyResults ]. AS [%$##@_Alias]
GROUP BY [%$##@_Alias].ServiceProvider;
This one from the Microsoft Access help file:
SELECT [CompanyName], [City]
FROM [Suppliers]
UNION
SELECT [CompanyName], [City]
FROM [Customers]
ORDER BY [City];