Error 3122 - Tried to execute a query that does not include the specified expression (1 Viewer)

Mr_Si

Registered User.
Local time
Today, 08:59
Joined
Dec 8, 2007
Messages
163
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
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:59
Joined
Feb 19, 2002
Messages
42,981
You are selecting fields that identify individual records but you are trying to sum their values. A query that aggregates (sums, averages, etc.) data cannot contain columns that would identify individual records. I think EntrantID may be the field that is causing the issue. Remove it and then group by ShowID and ClassID to see if that gives you the sum you are seeking.
 

Mr_Si

Registered User.
Local time
Today, 08:59
Joined
Dec 8, 2007
Messages
163
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!
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:59
Joined
Feb 19, 2002
Messages
42,981
You don't need the first query at all. Just use the second query against your original table. You removed ClassID and that allowed the sum to work. So you are summing the points for a specific entrant for a specific show across all classes.

You can sort the query descending on the sum() which will put the "winner" as the first record if that helps you. Or if you only want one record, you can add the Top predicate to select only one record.
Select Top 1 fld1, fld2, ....

Also see http://www.access-programmers.co.uk/forums/showthread.php?t=223893 #16 for my comments on embedded SQL.
 

Mr_Si

Registered User.
Local time
Today, 08:59
Joined
Dec 8, 2007
Messages
163
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
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:59
Joined
Feb 19, 2002
Messages
42,981
Class does not need to be included in the select clause to include it in the where clause. Unfortunately, it gets a little complicated because you have a Group By so Access ASSUMES you want a Having rather than a Where. But, you actually want a Where. The Where clause is applied BEFORE the aggregation and the Having clause is applied AFTER. To win the battle with Access and prevent it from changing the Where to a Having, you need to switch the query to SQL view and NEVER open it again in Design view. If this is too complicated, just use a query to the first part (but not a make table query) and refer to the first query in the second query. In Access, queries and tables are interchangeable for most purposes so you don't actually need to save a temp table.

Yes, you can use Top with Sum.
 

Mr_Si

Registered User.
Local time
Today, 08:59
Joined
Dec 8, 2007
Messages
163
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

 

Users who are viewing this thread

Top Bottom