SQL statement not working

[FinChase]

I am using a SQL statement to populate a MSFlexgrid, but I find that I am getting the wrong results I want to select and group and count all records by a field (clm_det_r) where one field ( chkConfirmed_r) is true. Instead, it is pulling all records where clm_det_r is not null.

This is the code I've written:

CatSQL = "SELECT [page2_3].[clm_det_r], COUNT([page2_3].[clm_det_r])FROM [Page2_3]GROUP BY [page2_3].[clm_det_r] WHERE [chkconfirmed_r] = TRUE"

What am I doing wrong?

[salvelinus]

Try:

CatSQL = "SELECT COUNT([page2_3].[clm_det_r])FROM [Page2_3] WHERE [chkconfirmed_r] = TRUE
GROUP BY [page2_3].[clm_det_r] "

[apps_tech]

That is correct..try salvelinus's reply...what u are doing wrong is when u are using group by statement in your sql, the selected fields should be group functions...not single fields...look into the following link for more info..
http://www.w3schools.com/sql/sql_groupby.asp

[RobDog888]

Also, I believe that when you use a GROUP BY clause the WHERE
should be replaced by a HAVING caluse.

CatSQL = "SELECT COUNT([page2_3].[clm_det_r]) "
CatSQL = CatSQL & "FROM [Page2_3] "
CatSQL = CatSQL & "GROUP BY [page2_3].[clm_det_r] "
CatSQL = CatSQL & "HAVING [chkconfirmed_r] = TRUE"

Hope this helps.

[techgnome]

Originally posted by RobDog888
Also, I believe that when you use a GROUP BY clause the WHERE
should be replaced by a HAVING caluse.

CatSQL = "SELECT COUNT([page2_3].[clm_det_r]) "
CatSQL = CatSQL & "FROM [Page2_3] "
CatSQL = CatSQL & "GROUP BY [page2_3].[clm_det_r] "
CatSQL = CatSQL & "HAVING [chkconfirmed_r] = TRUE"

Hope this helps.

ONLY when the filtered item is the aggregated result....IE, you want to only return items whose max value is greater than 10.
SELECT Field1, Field2, MAX(Field3) FROM tblSample
GROUP BY Field1, Field2
HAVING MAX(Field3) > 10
But it is still possible to use the WHERE clause to exclude records from the aggregation in the first place.... it just cannot go at the end ... it MUST come before the GROUP BY.
Also, if it is used in the HAVING, it MUST be in the SELECT clause as well.

TG

[RobDog888]

True, True.

[FinChase]

It is now giving me run-time error 3122 "You tried to execute a query that does not include the specified expression '[page2_3].[chkconfirmed_r]=-1' as part of an aggregate function."

[RobDog888]

Since the field in not part of the aggregate function you still need the where clause.

CatSQL = "SELECT COUNT([page2_3].[clm_det_r]) "
CatSQL = CatSQL & "FROM [Page2_3] "
CatSQL = CatSQL & "WHERE [chkconfirmed_r] = TRUE"
CatSQL = CatSQL & "GROUP BY [page2_3].[clm_det_r] "

This was discussed in the previous two postings.

[FinChase]

It is now giving me run-time error 3122 "You tried to execute a query that does not include the specified expression '[page2_3].[chkconfirmed_r]=-1' as part of an aggregate function."

[RobDog888]

Since we will be adding the field to the select list we can use the having clause.

CatSQL = "SELECT COUNT([page2_3].[clm_det_r]), "
CatSQL = CatSQL & "[Page2_3].[chkconfirmed_r]"
CatSQL = CatSQL & "FROM [Page2_3] "
CatSQL = CatSQL & "GROUP BY [page2_3].[clm_det_r], "
CatSQL = CatSQL & "[Page2_3].[chkconfirmed_r]"
CatSQL = CatSQL & "HAVING [Page2_3].[chkconfirmed_r] = TRUE"

[FinChase]

Originally posted by RobDog888
Since we will be adding the field to the select list we can use the having clause.

CatSQL = "SELECT COUNT([page2_3].[clm_det_r]), "
CatSQL = CatSQL & "[Page2_3].[chkconfirmed_r]"
CatSQL = CatSQL & "FROM [Page2_3] "
CatSQL = CatSQL & "GROUP BY [page2_3].[clm_det_r], "
CatSQL = CatSQL & "[Page2_3].[chkconfirmed_r]"
CatSQL = CatSQL & "HAVING [Page2_3].[chkconfirmed_r] = TRUE"

OK, I tried it this way, and it stopped giving me the 3122 error, but the results I am getting in my MSFlexgrid are wrong. It is displaying the correct count for the fields [Page2_3].[clm_det_r], but then it does not show the items grouped in [Page2_3].[clm_det_r]. Instead, it displays True

[FinChase]

Originally posted by FinChase
OK, I tried it this way, and it stopped giving me the 3122 error, but the results I am getting in my MSFlexgrid are wrong. It is displaying the correct count for the fields [Page2_3].[clm_det_r], but then it does not show the items grouped in [Page2_3].[clm_det_r]. Instead, it displays True

To clarify further, I am trying to pull a recordset that will contain two fields - a grouping of Error Categories (contained in the fields [clm_det_r] and a count of each Error Category. It's pulling the correct counts, but it's not grouping the error categories. If this helps, here's what I'm trying to do in a query built directly in Access:

SELECT PAGE2_3.CLM_DET_R, Count(PAGE2_3.CLM_DET_R) AS CountOfCLM_DET_R
FROM PAGE2_3
WHERE (((PAGE2_3.CHKCONFIRMED_r)=True))
GROUP BY PAGE2_3.CLM_DET_R;