Joey Sim Joey Sim - 1 month ago 6
SQL Question

MS-Access: your query does not include the specified expression as part of an aggregate function

I have a Select query to extract company information (name, ref no, Manager, Staff etc) from a SharePoint Documentation set. As only 2 staffs are allowed in this company, My goal is to check and select only those company who has on more than two staffs.

My sql code is as follows:

Select [Entity Name], [Ref No], [Group], [Partner], [Manager], [Staff]
[Covering Temp Access], [Expiry Date]
FROM [Table Name]
GROUP BY [Entity Name]
HAVING COUNT([Staffs])>2;


This returns Error:

Your query does not include the specified expression 'Ref No' as part of an aggregate function.

Thanks in advance for any help!

Answer

This is your query:

Select [Entity Name], [Ref No], [Group], [Partner], [Manager], [Staff],
       [Covering Temp Access], [Expiry Date]
FROM [Table Name]
GROUP BY [Entity Name]
HAVING COUNT([Staffs]) > 2;

Look at the SELECT. Most of the columns are not in the GROUP BY clause and are not the argument to an aggregation function.

Perhaps you intend:

SELECT [Entity Name], COUNT(*)
FROM [Table Name]
GROUP BY [Entity Name]
HAVING COUNT([Staffs]) > 2;

If you want all the information for a given entity, then use a join or in:

SELECT *
FROM [Table Name]
WHERE [Entity Name] IN (SELECT [Entity Name]
                        FROM [Table Name]
                        GROUP BY [Entity Name]
                        HAVING COUNT([Staffs]) > 2
                       );
Comments