Error 3122 (You tried..... aggregate function) (1 Viewer)

nkamp

Registered User.
Local time
Today, 05:40
Joined
Mar 2, 2007
Messages
15
Hello,

I have the following query:
Code:
SELECT
tblTempProject.project_number, 
tblTempProject.Project_name, 
tblTempProject.subsidiary, 
tblTempProject.division, 
tblTempProject.unit_number, 
tblTempProject.client_name, 
tblTempProject.resourcing_contact, 
tblTempProject.project_cat_type_code, 
tblTempJob.headline, 
tblTempJob.contact_name, 
tblTempJob.contact_staff_number, 
tblTempJob.contact_unit_number, 
tblTempJob.resourcing_team_email, 
tblTempJob.creation_date, 
tblTempJob.nominatio_date, 
tblTempJob.decision_date, 
tblTempJob.sar_status, 
tblTempJob.publication_level, 
tblTempJob.sar_number, 
tblTempJob.last_modified_timestamp, 
tblTempJob.last_updated_by, 
tblTempJob.requirements, 
tblTempJob.positions_filled, 
tblTempJob.closure_description, 
tblTempJob.other_inf, 
tblTempJob.description, 
tblTempJob.qual_exp, 
tblTempJob.Tag1, 
tblTempJob.Tag2, 
tblTempJob.Tag3, 
tblTempJob.Tag4
FROM tblTempJob, tblTempProject
WHERE tblTempJob.project_number = tblTempProject.project_number
AND      tblTempJob.project_category_type_code = tblTempProject.project_cat_type_code
AND      tblTempJob.project_company_code =  tblTempProject.subsidiary
AND      tblTempProject.subsidiary='NL'
AND     (tblTempJob.sar_status='FIR' Or tblTempJob.sar_status='HOL' Or tblTempJob.sar_status='PRO25' Or tblTempJob.sar_status='PRO50' Or tblTempJob.sar_status='PRO75')
AND      tblTempJob.project_company_code=[tblTempProject].[subsidiary]
AND      tblTempJob.project_category_type_code=[tblTempProject].[project_cat_type_code]
AND      tblTempJob.project_number=[tblTempProject].[project_number]
AND      tblTempJob.external_resourcing=-1
GROUP BY tblTempProject.project_number
ORDER BY tblTempJob.sar_status;

When execute this query then I get the following error:
"You tried to execute a query that does not include the specified expression <name> as part of an aggregate function. (Error 3122)"

When delete the line: GROUP BY tblTempProject.project_number, then it works.
I don't know what I must do to get this right so that I have every project one time in the result.

Thanks in advance,

Nico
 

Dennisk

AWF VIP
Local time
Today, 04:40
Joined
Jul 22, 2004
Messages
1,649
when using a totals query with criteria, the criteria column needs to be set to a where category (in the total row) and set to not visible.
 

nkamp

Registered User.
Local time
Today, 05:40
Joined
Mar 2, 2007
Messages
15
Hello Dennisk,

huhh? I think it depends more on me then on you but I don't know what you mean? what do you mean by a totals query? I did some investigations. The next example is working:
Code:
SELECT
tblTempJob.sar_status
FROM tblTempJob
GROUP BY tblTempJob.sar_status;

But if I add a field after tblTempJob.sar_status then I get this error.
I read somewhere in the help that you need to add all the fields in the GROUP by!!??? Has this to do with DAO in stead of ADO??

Thanks in advance,

Nico
 

Dennisk

AWF VIP
Local time
Today, 04:40
Joined
Jul 22, 2004
Messages
1,649
nothing to do with ado which is a record access library.
Are you not using the query designer?
 

nkamp

Registered User.
Local time
Today, 05:40
Joined
Mar 2, 2007
Messages
15
Sometimes but not on this moment because I could not found the group by or I had still problems with it. So I thought I do it on the PHP/MySQL way!

I have put all the SELECT fields in the GROUP BY and then it's working but I have still doplicated project_numbers in the result!!

What is the alternativ? I have tried DISTINCT (tablename.fieldname), other fields. The result is I have still duplicated project numbers.

On this moment I don't know how to solve it.

Thanks in advance,

Nico
 

Dennisk

AWF VIP
Local time
Today, 04:40
Joined
Jul 22, 2004
Messages
1,649
don't forget you have to convert a select query to a totals query by selecting view then totals on the menu. then you can specify what is required for each column, ie group, max, min, expression, where etc.
 

bloomy555

New member
Local time
Yesterday, 21:40
Joined
Jan 27, 2008
Messages
8
Hello Dennisk,

huhh? I think it depends more on me then on you but I don't know what you mean? what do you mean by a totals query? I did some investigations. The next example is working:
Code:
SELECT
tblTempJob.sar_status
FROM tblTempJob
GROUP BY tblTempJob.sar_status;

But if I add a field after tblTempJob.sar_status then I get this error.
I read somewhere in the help that you need to add all the fields in the GROUP by!!??? Has this to do with DAO in stead of ADO??

Thanks in advance,

Nico

YES IT HAS.........I have been in your place before.
 

neileg

AWF VIP
Local time
Today, 04:40
Joined
Dec 4, 2002
Messages
5,975
Perhaps I can shine a little light in here. An aggregate query is one that calculates some form of summary of the data, typically the sum of a field. The GROUP BY clause in your query indicates that it is an aggregate query, but you have not used an aggregate operator (SUM for example) anywhere in the query.

To answer this question:
I don't know what I must do to get this right so that I have every project one time in the result.
You need to set the unique values property of the query to Yes. This will change the first clause of your query to:
Code:
SELECT DISTINCT
tblTempProject.project_number, 
tblTempProject.Project_name, 
etc.
 

bhavdahl

Registered User.
Local time
Yesterday, 23:40
Joined
Feb 4, 2008
Messages
17
when using a totals query with criteria, the criteria column needs to be set to a where category (in the total row) and set to not visible.

Thank you!!!! ...I was having the same problem. Microsoft should get you to write the text for their error messages. :)
 

Users who are viewing this thread

Top Bottom