Union ALL with Count

[Juan Schwartz]

I am getting an error message with the following query. Could someone
post an example of how to do what I'm attempting so that I can
understand how it's supposed to work?

I have 5 tables. I am trying to do a union on all 5 and add up the
counts of records in all 5 and total them by Name. The output should
end up as

Name Count
---------------------------
Name1 10
Name2 24


Error Message:You tried to execute a query that does not include the
specified function 'Name' as part of an aggragate function.

SELECT MonitorName As Name, Count([MonitorEntry(Business)].MonitorID)
AS Count FROM [MonitorEntry(Business)],[MonitorID]
WHERE [MonitorID].[MonitorID] = [MonitorEntry(Business)].MonitorID

UNION ALL

SELECT MonitorName As Name,Count([MonitorEntry(Collection)].MonitorID)
AS Count FROM [MonitorEntry(Collection)],[MonitorID]
WHERE [MonitorID].[MonitorID] = [MonitorEntry(Collection)].MonitorID

UNION ALL

SELECT MonitorName As Name,Count([MonitorEntry(Retention)].MonitorID)
AS Count FROM [MonitorEntry(Retention)],[MonitorID]
WHERE [MonitorID].[MonitorID] = [MonitorEntry(Retention)].MonitorID

UNION ALL

SELECT MonitorName As Name,Count([MonitorEntry(Sales)].MonitorID) AS
Count FROM [MonitorEntry(Sales)],[MonitorID]
WHERE [MonitorID].[MonitorID] = [MonitorEntry(Sales)].MonitorID

UNION ALL

SELECT MonitorName As Name,Count([MonitorEntry(TechSupport)].MonitorID)
AS Count FROM [MonitorEntry(TechSupport)],[MonitorID]
WHERE [MonitorID].[MonitorID] = [MonitorEntry(TechSupport)].MonitorID
GROUP BY Name

 

[Juan Schwartz]

I had been goofing around with it when I posted that query...

Here is the original Query... When I take out the Counts, it works and
displays the names correctly...

SELECT MonitorName, Count([MonitorEntry(Business)].MonitorID) AS Count
FROM [MonitorEntry(Business)],[MonitorID]
WHERE [MonitorID].[MonitorID] = [MonitorEntry(Business)].MonitorID

UNION

SELECT MonitorName,Count([MonitorEntry(Collection)].MonitorID) AS Count
FROM [MonitorEntry(Collection)],[MonitorID]
WHERE [MonitorID].[MonitorID] = [MonitorEntry(Collection)].MonitorID

UNION

SELECT MonitorName,Count([MonitorEntry(Retention)].MonitorID) AS Count
FROM [MonitorEntry(Retention)],[MonitorID]
WHERE [MonitorID].[MonitorID] = [MonitorEntry(Retention)].MonitorID

UNION

SELECT MonitorName,Count([MonitorEntry(Sales)].MonitorID) FROM
[MonitorEntry(Sales)],[MonitorID]
WHERE [MonitorID].[MonitorID] = [MonitorEntry(Sales)].MonitorID

UNION

SELECT MonitorName,Count([MonitorEntry(TechSupport)].MonitorID) AS
Count FROM [MonitorEntry(TechSupport)],[MonitorID]
WHERE [MonitorID].[MonitorID] = [MonitorEntry(TechSupport)].MonitorID

GROUP BY MonitorName ORDER BY MonitorName

 

[Roger Carlson]

Two things:
1) "Name" is a reserved word in Access. Use a different alias.
2) You have to do the Group By on EACH query in the Union.

SELECT MonitorName, Count([MonitorEntry(Business)].MonitorID)
AS Count FROM [MonitorEntry(Business)],[MonitorID]
WHERE [MonitorID].[MonitorID] = [MonitorEntry(Business)].MonitorID
GROUP BY MonitorName
UNION ALL
....etc.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Juan Schwartz]

Any way to Sum all of the results in the query and group by name in the
end?

 

[Roger Carlson]

Not sure what you mean, can you show a sample?

 

[Juan Schwartz]

This is my query as it stands now. I made the unions part of a subquery
and am requesting the name and sum of the count from each query. I want
to pull the count of records from the 5 tables below while adding up
the Count of records in these queries.

SELECT [%$##@_Alias].MonitorName, Sum([%$##@_Alias].Count) AS Monitors
FROM [SELECT MonitorName, Count([MonitorEntry(Business)].MonitorID) AS
Count FROM [MonitorEntry(Business)],[MonitorID]
WHERE [MonitorID].[MonitorID] = [MonitorEntry(Business)].MonitorID AND
[MonitorEntry(Business)].[Calldate] BETWEEN [Enter Start
Date(mm/dd/yy): ] AND [Enter Stop Date(mm/dd/yy): ]
GROUP BY MonitorName

UNION

SELECT MonitorName,Count([MonitorEntry(Collection)].MonitorID) AS Count
FROM [MonitorEntry(Collection)],[MonitorID]
WHERE [MonitorID].[MonitorID] = [MonitorEntry(Collection)].MonitorID
AND [MonitorEntry(Collection)].[Calldate] BETWEEN [Enter Start
Date(mm/dd/yy): ] AND [Enter Stop Date(mm/dd/yy): ]
GROUP BY MonitorName

UNION

SELECT MonitorName,Count([MonitorEntry(Retention)].MonitorID) AS Count
FROM [MonitorEntry(Retention)],[MonitorID]
WHERE [MonitorID].[MonitorID] = [MonitorEntry(Retention)].MonitorID AND
[MonitorEntry(Retention)].[Calldate] BETWEEN [Enter Start
Date(mm/dd/yy): ] AND [Enter Stop Date(mm/dd/yy): ]
GROUP BY MonitorName

UNION

SELECT MonitorName,Count([MonitorEntry(Sales)].MonitorID) FROM
[MonitorEntry(Sales)],[MonitorID]
WHERE [MonitorID].[MonitorID] = [MonitorEntry(Sales)].MonitorID AND
[MonitorEntry(Sales)].[Calldate] BETWEEN [Enter Start Date(mm/dd/yy): ]
AND [Enter Stop Date(mm/dd/yy): ]
GROUP BY MonitorName

UNION

SELECT MonitorName,Count([MonitorEntry(TechSupport)].MonitorID) AS
Count FROM [MonitorEntry(TechSupport)],[MonitorID]
WHERE [MonitorID].[MonitorID] = [MonitorEntry(TechSupport)].MonitorID
AND [MonitorEntry(TechSupport)].[Calldate] BETWEEN [Enter Start
Date(mm/dd/yy): ] AND [Enter Stop Date(mm/dd/yy): ]
GROUP BY MonitorName]. AS [%$##@_Alias]
GROUP BY [%$##@_Alias].MonitorName;

 

[Roger Carlson]

Is this working? I was going to suggest the ". As AliasName" business. It
would be good to know if it works. Otherwise, I don't really see a
solution.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[John Spencer]

I'm pretty sure that this fails, as the []. as syntax in Access won't allow
any [] within the subquery.

If you are willing to do this with two queries then I would suggest using a
UNION ALL query to build the entire list and then using that as a source for
your aggregate query. Note that I rewrote the first two queries in the
Union query to use the Join syntax - which I think would be more efficient.

SELECT MonitorName
FROM [MonitorEntry(Business)] INNER JOIN [MonitorID]
ON [MonitorID].[MonitorID] = [MonitorEntry(Business)].MonitorID
WHERE [MonitorEntry(Business)].[Calldate] BETWEEN [Enter Start
Date(mm/dd/yy): ] AND [Enter Stop Date(mm/dd/yy): ]

UNION ALL

SELECT MonitorName
FROM [MonitorEntry(Collection)] INNER JOIN [MonitorID]
ON [MonitorID].[MonitorID] = [MonitorEntry(Collection)].MonitorID
WHERE [MonitorEntry(Collection)].[Calldate] BETWEEN [Enter Start
Date(mm/dd/yy): ] AND [Enter Stop Date(mm/dd/yy): ]

UNION ALL

SELECT MonitorName
FROM [MonitorEntry(Retention)],[MonitorID]
WHERE [MonitorID].[MonitorID] = [MonitorEntry(Retention)].MonitorID AND
[MonitorEntry(Retention)].[Calldate] BETWEEN [Enter Start
Date(mm/dd/yy): ] AND [Enter Stop Date(mm/dd/yy): ]


UNION ALL

SELECT MonitorName
FROM [MonitorEntry(Sales)],[MonitorID]
WHERE [MonitorID].[MonitorID] = [MonitorEntry(Sales)].MonitorID AND
[MonitorEntry(Sales)].[Calldate] BETWEEN [Enter Start Date(mm/dd/yy): ]
AND [Enter Stop Date(mm/dd/yy): ]
GROUP BY MonitorName

UNION ALL

SELECT MonitorName
FROM [MonitorEntry(TechSupport)],[MonitorID]
WHERE [MonitorID].[MonitorID] = [MonitorEntry(TechSupport)].MonitorID
AND [MonitorEntry(TechSupport)].[Calldate] BETWEEN [Enter Start
Date(mm/dd/yy): ] AND [Enter Stop Date(mm/dd/yy): ]

Save that as qUniMonitors. And then your query becomes

SELECT MonitorName, Count(MonitorName) as CountOf
FROM qUniMonitors
GROUP BY MonitorName

This is my query as it stands now. I made the unions part of a subquery
and am requesting the name and sum of the count from each query. I want
to pull the count of records from the 5 tables below while adding up
the Count of records in these queries.

SELECT [%$##@_Alias].MonitorName, Sum([%$##@_Alias].Count) AS Monitors
FROM [SELECT MonitorName, Count([MonitorEntry(Business)].MonitorID) AS
Count FROM [MonitorEntry(Business)],[MonitorID]
WHERE [MonitorID].[MonitorID] = [MonitorEntry(Business)].MonitorID AND
[MonitorEntry(Business)].[Calldate] BETWEEN [Enter Start
Date(mm/dd/yy): ] AND [Enter Stop Date(mm/dd/yy): ]
GROUP BY MonitorName

UNION

SELECT MonitorName,Count([MonitorEntry(Collection)].MonitorID) AS Count
FROM [MonitorEntry(Collection)],[MonitorID]
WHERE [MonitorID].[MonitorID] = [MonitorEntry(Collection)].MonitorID
AND [MonitorEntry(Collection)].[Calldate] BETWEEN [Enter Start
Date(mm/dd/yy): ] AND [Enter Stop Date(mm/dd/yy): ]
GROUP BY MonitorName

UNION

SELECT MonitorName,Count([MonitorEntry(Retention)].MonitorID) AS Count
FROM [MonitorEntry(Retention)],[MonitorID]
WHERE [MonitorID].[MonitorID] = [MonitorEntry(Retention)].MonitorID AND
[MonitorEntry(Retention)].[Calldate] BETWEEN [Enter Start
Date(mm/dd/yy): ] AND [Enter Stop Date(mm/dd/yy): ]
GROUP BY MonitorName

UNION

SELECT MonitorName,Count([MonitorEntry(Sales)].MonitorID) FROM
[MonitorEntry(Sales)],[MonitorID]
WHERE [MonitorID].[MonitorID] = [MonitorEntry(Sales)].MonitorID AND
[MonitorEntry(Sales)].[Calldate] BETWEEN [Enter Start Date(mm/dd/yy): ]
AND [Enter Stop Date(mm/dd/yy): ]
GROUP BY MonitorName

UNION

SELECT MonitorName,Count([MonitorEntry(TechSupport)].MonitorID) AS
Count FROM [MonitorEntry(TechSupport)],[MonitorID]
WHERE [MonitorID].[MonitorID] = [MonitorEntry(TechSupport)].MonitorID
AND [MonitorEntry(TechSupport)].[Calldate] BETWEEN [Enter Start
Date(mm/dd/yy): ] AND [Enter Stop Date(mm/dd/yy): ]
GROUP BY MonitorName]. AS [%$##@_Alias]
GROUP BY [%$##@_Alias].MonitorName;

 

[Juan Schwartz]

It has been working for some time now...