Malloy1446
asked on
Display data based on a sql count statement
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.VRNotEligibl e 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.
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.VRNotEligibl
"GROUP BY tblGeneral.City, tblVirtualRef.ChartID;"
Set objCountChartID = Server.CreateObject("ADODB
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.
ASKER
I have never use two FROM statements in the same SQL clause. I have tried to add the following, but cannot get the right syntax...
sqlCountChartID ="SELECT tblGeneral.ChartID, t1.City, tblGeneral.CountChartID, tblGeneral.State " & _
"FROM tblGeneral INNER JOIN (SELECT tblVirtualRef.ChartID, tblGeneral.City, Count(tblVirtualRef.VRID) AS CountChartID " & _
"FROM tblVirtualRef INNER JOIN tblGeneral ON tblGeneral.ChartID= tblVirtualRef.ChartID WHERE tblVirtualRef.VRNotEligibl e LIKE 'Y' " & _
"GROUP BY tblGeneral.City, tblVirtualRef.ChartID)"
Set objCountChartID = Server.CreateObject("ADODB .Recordset ")
objCountChartID.Open sqlCountChartID, objConn
Which generates the "Website cannot display the page" error
sqlCountChartID ="SELECT tblGeneral.ChartID, t1.City, tblGeneral.CountChartID, tblGeneral.State " & _
"FROM tblGeneral INNER JOIN (SELECT tblVirtualRef.ChartID, tblGeneral.City, Count(tblVirtualRef.VRID) AS CountChartID " & _
"FROM tblVirtualRef INNER JOIN tblGeneral ON tblGeneral.ChartID= tblVirtualRef.ChartID WHERE tblVirtualRef.VRNotEligibl
"GROUP BY tblGeneral.City, tblVirtualRef.ChartID)"
Set objCountChartID = Server.CreateObject("ADODB
objCountChartID.Open sqlCountChartID, objConn
Which generates the "Website cannot display the page" error
You are actually not using two FROM statements in the same query (that would be illegal). You are creating a temporary table (t1) out of your aggregate SELECT statement. You are then joining the results of that temporary table in your main query to pull all of the fields you want.
And I apologize for my brain right now. Just back from a late flight and little sleep...I totally left out the ON condition for join statement to the temp table. Duh...!!!!
...) t1 ON tblGeneral.ChartID = t1.ChartID
And I apologize for my brain right now. Just back from a late flight and little sleep...I totally left out the ON condition for join statement to the temp table. Duh...!!!!
...) t1 ON tblGeneral.ChartID = t1.ChartID
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks. The solution to add the fields to the group by making adding any extra fields real easy!
...GROUP BY tblGeneral.City, tblVirtualRef.ChartID, tblGeneral.State
The more difficult approach, and arguably better, is to use a temp table to isolate what you actually want to group by from what you only want to select. This will be necessary if your data set will cause count values to change with the added group by. So the approach would be:
SELECT t1.ChartID, t1.City, t1.CountChartID, tblGeneral.State FROM
tblGeneral INNER JOIN
(SELECT tblVirtualRef.ChartID, tblGeneral.City, Count(tblVirtualRef.VRID) AS CountChartID
FROM tblVirtualRef INNER JOIN tblGeneral ON tblGeneral.ChartID= tblVirtualRef.ChartID WHERE tblVirtualRef.VRNotEligibl
GROUP BY tblGeneral.City, tblVirtualRef.ChartID) t1
My brain is mush right now, so I hope I didn't flubber the syntax.