Error 3122 - Tried to execute a query that does not include the specified expression

Mr_Si · Apr 4, 2012

Hi all,

"you tried to execute a query that does not include the specified expression 'lngzShowID' as part of an aggregate function"

I'm trying to do a totals / summation in SQL but I am getting the above error.

My code is:

Code:

[SIZE=2]            Dim strRHSBMSelect As String
            Dim strRHSBMWhere As String
            Dim strRHSBMSQL As String
            
                strRHSBMSelect = "SELECT tblShowEntrantClass.lngzShowID, " _
                                & "tblShowEntrantClass.lngzEntrantID, " _
                                & "tblShowEntrantClass.lngzClassID, " _
                                & "SUM(tblShowEntrantClass.numPoints) " _
                                & "FROM tblShowEntrantClass " _
                                & "WHERE "
                strRHSBMWhere = "tblShowEntrantClass.lngzShowID = " & Me.lngzShowID & " AND " _
                                    & "lngzClassID BETWEEN 1 AND 13 " _
                                    & "GROUP BY lngzEntrantID"
                strRHSBMSQL = strRHSBMSelect & strRHSBMWhere & ";"
                
                'print sql statement
                MsgBox strRHSBMSQL
                CurrentDb.QueryDefs("qryShowEntrantClassPoints").SQL = strRHSBMSQL
                DoCmd.OpenForm "frmShowEntrantClassPoints", , , , , acDialog[/SIZE]

I tried to change the group by statement to "GROUP BY lngzEntrantID, lngzShowID, lngzClassID" but then it doesn't do what I want.

I don't understand what I need to do.

Please help!

Thanks in advance.
Simon

Mr_Si · Apr 4, 2012

Hi Pat,

Thank you for your response. Sadly I needed it to group by the Entrant as I then need to find which entrant has the highest total (another question regarding that will surely arise soon).

I have found that to do this, I needed to do it in a staged approach:

I had to make a new table from the first query with no sum and then in the new table, made a query on this new table, to do the sum... This did the sum correctly.

However, now I need to find the MAX value.

My code so far is:

Code:

Dim strRHSBMSelect As String
            Dim strRHSBMSelect2 As String
            Dim strRHSBMWhere As String
            Dim strRHSBMSQL As String
            Dim strRHSBMSQL2 As String
            
                strRHSBMSelect = "SELECT tblShowEntrantClass.lngzShowID, " _
                                & "tblShowEntrantClass.lngzEntrantID, " _
                                & "tblShowEntrantClass.lngzClassID, " _
                                & "tblShowEntrantClass.numPoints " _
                                & "INTO tblRHSBM " _
                                & "FROM tblShowEntrantClass " _
                                & "WHERE "
                strRHSBMWhere = "tblShowEntrantClass.lngzShowID = " & Me.lngzShowID & " AND " _
                                    & "lngzClassID BETWEEN 1 AND 13"
                strRHSBMSQL = strRHSBMSelect & strRHSBMWhere & ";"
                
                'print sql statement
                MsgBox strRHSBMSQL
                
                'replace existing query with the above one
                CurrentDb.QueryDefs("qryShowEntrantClassPoints2").SQL = strRHSBMSQL
                
                strRHSBMSelect2 = "SELECT tblRHSBM.lngzShowID, " _
                                    & "tblRHSBM.lngzEntrantID, " _
                                    & "SUM(tblRHSBM.numPoints) As SumOfnumPoints " _
                                    & "FROM tblRHSBM " _
                                    & "Group By tblRHSBM.lngzShowID, tblRHSBM.lngzEntrantID;"
                
                strRHSBMSQL2 = strRHSBMSelect2

                'Print SQL statement
                MsgBox strRHSBMSQL2
                
                'replace existing query with the above one
                CurrentDb.QueryDefs("qryRHSBM").SQL = strRHSBMSQL2
                                    
                'DoCmd.OpenForm "frmShowEntrantClassPoints", , , , , acDialog
                
                strSELECT = "SELECT tblShowEntrantClass.lngzShowID, " _
                        & "tblShowEntrantClass.lngzEntrantID, " _
                        & "tblShowEntrantClass.lngzClassID, " _
                        & "tblShowEntrantClass.numPoints " _
                        & "FROM tblShowEntrantClass;"
                
                '(reset query)
                CurrentDb.QueryDefs("qryShowEntrantClassPoints").SQL = strSELECT
                
                DoCmd.OpenForm "frmRHSBM", , , , , acDialog

I know it's probably dirty coding but I don't know any better at the moment!

Mr_Si · Apr 4, 2012

Hi Pat,

Sorry, no, there are lots of classes but different awards for different ranges of classes. The class field needed to be included, in order to have it as part of the WHERE clause to be classes 1 - 13.

I found that I needed to do two queries to get it that far, so that the specifics were correct.

I shall look in to the Select Top syntax - can I use it with SUM?

Thanks you ever so much for your help.

Kind Regards,
Simon

Mr_Si · Apr 5, 2012

Wow! That works!

My new code is a single query:

Code:

            Dim strRHSBMSelect As String
            Dim strRHSBMWhere As String
            Dim strRHSBMSQL As String
            
                strRHSBMSelect = "SELECT TOP 1 tblShowEntrantClass.lngzShowID, " _
                                & "tblShowEntrantClass.lngzEntrantID, " _
                                & "SUM(tblShowEntrantClass.numPoints) " _
                                & "FROM tblShowEntrantClass " _
                                & "WHERE "
                strRHSBMWhere = "tblShowEntrantClass.lngzShowID = " & Me.lngzShowID & " AND " _
                                    & "lngzClassID BETWEEN 1 AND 13 " _
                                    & "GROUP BY lngzShowID, lngzEntrantID"
                strRHSBMSQL = strRHSBMSelect & strRHSBMWhere & ";"
                
                'print sql statement
                MsgBox strRHSBMSQL
                
                'replace existing query with the above one
                CurrentDb.QueryDefs("qryShowEntrantClassPoints").SQL = strRHSBMSQL
                                    
                DoCmd.OpenForm "frmShowEntrantClassPoints", , , , , acDialog
                Me.lngzEntrantID = Form_frmShowEntrantClassPoints.lngzEntrantID

The only thing that confuses me now is why I get a #Name? in the summation field

Error 3122 - Tried to execute a query that does not include the specified expression

Mr_Si

Registered User.

Mr_Si

Registered User.

Mr_Si

Registered User.

Mr_Si

Registered User.

Similar threads

Users who are viewing this thread