"not part of aggregate function"

[jd_boss_hogg]

Hi All,

I'm getting the 'not part of aggregate function' error on a SQl. After looking through previous posts, i can see that it's because i'm grouping some of my fields (using SUM) but not others. The trouble i have, is i don't understand what i have to do with them in the SQL.

I have a report which has a few fields in the header that calculate some sum values. Further down the report, i then want to list these individually in the 'details' section of the report, grouped by [worktype].

Here is the simplified version of the code...

 [FONT=Calibri]SELECT 
[/FONT]
[FONT=Calibri]Sum  (IIf   (([jb-2001].WORKTYPE="Chrome Mask")    ,  [jb-2001].NET,   0  )) AS Glass1, [/FONT]
  [FONT=Calibri] [/FONT]
  [FONT=Calibri]Sum  (IIf   (([jb-2001].WORKTYPE="Chrome Mask")    ,  [jb-2001].NET,   0  )) AS Glass2,[/FONT]
[FONT=Calibri]
[/FONT]
[FONT=Calibri][customer], [name]
[/FONT]
[FONT=Calibri]
[/FONT]
[FONT=Calibri]FROM [jb-2001];[/FONT]
[FONT=Calibri]

[/FONT]


So, how can i use [customer] and [name] in the details section of the report, because at the moment i get the 'not part of aggregate function' error?


Hoping someone can shed some light ! Thanks in advance...

 

[georgedwilkinson]

You must use "group by".

Assuming the rest of your code is right:

[FONT=Calibri]SELECT 
[/FONT][FONT=Calibri]Sum  (IIf   (([jb-2001].WORKTYPE="Chrome Mask")    ,  
[jb-2001].NET,   0  )) AS Glass1, [/FONT]
[FONT=Calibri]Sum  (IIf   (([jb-2001].WORKTYPE="Chrome Mask")    ,  [jb-2001].NET,   0  )) AS Glass2,[/FONT]
[FONT=Calibri][customer], 
[name]
[/FONT][FONT=Calibri]FROM [jb-2001]
group by
[/FONT][FONT=Calibri][customer], 
[name][/FONT]
[FONT=Calibri];[/FONT]

 

[jd_boss_hogg]

Hi George,

Thanks for the feedback.

My code was highly simplified from the actual code, however when i tried this method on both my existing code AND the highly simplified one, i get the following:-

"reserved error -1524".

I've googled that, but can't find anything that really means anything to me. My tables are linked, and i've just realised that one of my fields in the table is 'date' which i beleive to be a reserved name. I'll try to fix it, but i don't really understand because i only get this message when i use the 'order by' code...

 

[jd_boss_hogg]

.... sorry, i meant 'group by' and not 'order by'. I't looks like any other aggregate that i try (for example FIRST or LAST) gives me this error....

 

[gemma-the-husky]

are you in acess? can you not get this working using (a series of) visual queries -

 

[jd_boss_hogg]

Hi dave - Sorry, somewhat of a newby and not uptodate with the terminology.

I'm using access 2007. I have a report with an underlying query. I don't understand "visual queries" or how to put them in series?

 

[namliam]

Visual: As in you can see the query in the database screen and/or using the designer to build them

Put them in Series:
If you cannot make (complex) queries work, you can do a query on a query on a query, you can 'stack' or 'put them in series' indefinatly.

 

[boblarson]

I've googled that, but can't find anything that really means anything to me. My tables are linked, and i've just realised that one of my fields in the table is 'date' which i beleive to be a reserved name. I'll try to fix it, but i don't really understand because i only get this message when i use the 'order by' code...

If you use a reserved word for a field name then it always has to be enclosed by square brackets. For example:

MyTableName.[Date]

instead of

MyTableName.Date

but is best to not have things named that are reserved words. (using NAME is one that doesn't always work with square brackets)