more aggregate function questions

[boutwater]

I'd like to do this:
SELECT Month, itemnumber, max(cartoncount)
FROM cartonsBySKUPerMonth
GROUP BY itemnumber
ORDER BY ItemNumber

but it complains about the "Month". How can I show this? Thanks,

Ben

[boblarson]

Because Month is an Access Reserved Word, you need to put it in square brackets (a good reason to avoid using Access Reserved Words and spaces in your object or field names).

SELECT [Month], itemnumber...etc.

[RayMilhon]

I normally use the field type as the first 3 characters of the fieldname that way it'll never be a reserved word.

Your month would be intmonth.

[boutwater]

I put it in brackets, but still get the error complaining that "Month" isn't an aggregate function. I know it isn't, but i wasn it displayed when the max is returned so i know which month the max occurred in.

[boblarson]

You need to include MONTH in the Group By clause.

[boutwater]

I dion't want to group by the Month though...i just want it to show up based on what I am grouping by.

[boblarson]

I dion't want to group by the Month though...i just want it to show up based on what I am grouping by.

With a Group By Clause you either have to include that field in it or it can't be included in the query.