Display data based on a sql count statement
Posted on 2013-05-22
I have a SQL statement counting the number of records based on certain criteria.
My SQL statement is:
sqlCountChartID ="SELECT tblVirtualRef.ChartID, tblGeneral.City, Count(tblVirtualRef.VRID) AS CountChartID " & _
"FROM tblVirtualRef INNER JOIN tblGeneral ON tblGeneral.ChartID= tblVirtualRef.ChartID " & _
"WHERE tblVirtualRef.VRNotEligible LIKE 'Y' " & _
"GROUP BY tblGeneral.City, tblVirtualRef.ChartID;"
Set objCountChartID = Server.CreateObject("ADODB.Recordset")
objCountChartID.Open sqlCountChartID, objConn
When I add tblGeneral.State to the SQL statement (right after tblGeneral.City) I get the following error.
Microsoft JET Database Engine error '80040e21'
You tried to execute a query that does not include the specified expression 'State' as part of an aggregate function.