MS Access GROUP BY problem

Question

Hi, I'm trying to execute this query:

SELECT YOUNGPERSON.IDNUMBER, YOUNGPERSON.SURNAME

FROM YOUNGPERSON

GROUP BY YOUNGPERSON.SURNAME;

but I get the following error from MS Access(2007) :

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

 Microsoft's documentation implies that an aggregate function is not mandatory for the use of the GROUP BY clause. What;s up?!!

Answer 1

You have to include all fields in the GROUP BY statement that do not have Avg, Count, Sum, Min, Max, etc.

What are you trying to accomplish?

Answer 2

Thanks for your response. I can't achieve what I want using the group by. So I'll find another way!

Answer 3

You can get the same results without using GROUP BY

SELECT DISTINCT YOUNGPERSON.IDNUMBER, YOUNGPERSON.SURNAME

FROM YOUNGPERSON

OR

SELECT YOUNGPERSON.IDNUMBER, YOUNGPERSON.SURNAME

FROM YOUNGPERSON

GROUP BY YoungPerson.IdNumber, YOUNGPERSON.SURNAME