Link to home
Start Free TrialLog in
Avatar of Malloy1446
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.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.
Avatar of mds-cos
mds-cos
Flag of United States of America image

The easy way is to put tblGeneral.State into your GROUP BY clause...assuming your data set is such that this will not affect your count values. (yes, I understand you are not trying to group by State).  So you would have:

...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.VRNotEligible LIKE 'Y'
GROUP BY tblGeneral.City, tblVirtualRef.ChartID) t1


My brain is mush right now, so I hope I didn't flubber the syntax.
Avatar of Malloy1446
Malloy1446

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.VRNotEligible 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
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
ASKER CERTIFIED SOLUTION
Avatar of mds-cos
mds-cos
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks. The solution to add the fields to the group by making adding any extra fields real easy!