can't get query to group

here is the sql.

can someone help me determine why the query isn't grouping by GoupNumber?
The GroupName is the same for the GroupNumber. ID is unique.

SELECT Mid([ID],3,2) AS GroupNumber, Accounts.GroupName
FROM Accounts
GROUP BY Mid([ID],3,2), Accounts.GroupName, Accounts.ID
HAVING (((Accounts.ID) Like "01*"));

Example
ID GroupNumber GroupName
010101 01 red
010102 01 red
010201 02 blue
010301 03 green

It is grouping by "GroupNumber", then GroupName, then ID. Since ID is unique,
it will return every record. If you want to group by GroupNumber and
GroupName, run this:

SELECT Mid([ID],3,2) AS GroupNumber, Accounts.GroupName
FROM Accounts
GROUP BY Mid([ID],3,2), Accounts.GroupName
HAVING (((Accounts.ID) Like "01*"));

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"gator" wrote:

here is the sql.

can someone help me determine why the query isn't grouping by GoupNumber?
The GroupName is the same for the GroupNumber. ID is unique.

SELECT Mid([ID],3,2) AS GroupNumber, Accounts.GroupName
FROM Accounts
GROUP BY Mid([ID],3,2), Accounts.GroupName, Accounts.ID
HAVING (((Accounts.ID) Like "01*"));

Example
ID GroupNumber GroupName
010101 01 red
010102 01 red
010201 02 blue
010301 03 green

error:
You tried to execute a query that does not include the specified expression
'Accounts.ID Like "01*"' as part of an aggregate function.

"Jerry Whittle" wrote:

It is grouping by "GroupNumber", then GroupName, then ID. Since ID is unique,
it will return every record. If you want to group by GroupNumber and
GroupName, run this:

SELECT Mid([ID],3,2) AS GroupNumber, Accounts.GroupName
FROM Accounts
GROUP BY Mid([ID],3,2), Accounts.GroupName
HAVING (((Accounts.ID) Like "01*"));

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"gator" wrote:

here is the sql.

can someone help me determine why the query isn't grouping by GoupNumber?
The GroupName is the same for the GroupNumber. ID is unique.

SELECT Mid([ID],3,2) AS GroupNumber, Accounts.GroupName
FROM Accounts
GROUP BY Mid([ID],3,2), Accounts.GroupName, Accounts.ID
HAVING (((Accounts.ID) Like "01*"));

Example
ID GroupNumber GroupName
010101 01 red
010102 01 red
010201 02 blue
010301 03 green

SELECT Mid([ID],3,2) AS GroupNumber, Accounts.GroupName
FROM Accounts
WHERE (((Accounts.ID) Like "01*"))
GROUP BY Mid([ID],3,2), Accounts.GroupName



Vanderghast, Access MVP





"gator" wrote in message
...
error:
You tried to execute a query that does not include the specified
expression
'Accounts.ID Like "01*"' as part of an aggregate function.

"Jerry Whittle" wrote:

It is grouping by "GroupNumber", then GroupName, then ID. Since ID is
unique,
it will return every record. If you want to group by GroupNumber and
GroupName, run this:

SELECT Mid([ID],3,2) AS GroupNumber, Accounts.GroupName
FROM Accounts
GROUP BY Mid([ID],3,2), Accounts.GroupName
HAVING (((Accounts.ID) Like "01*"));

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"gator" wrote:

here is the sql.

can someone help me determine why the query isn't grouping by
GoupNumber?
The GroupName is the same for the GroupNumber. ID is unique.

SELECT Mid([ID],3,2) AS GroupNumber, Accounts.GroupName
FROM Accounts
GROUP BY Mid([ID],3,2), Accounts.GroupName, Accounts.ID
HAVING (((Accounts.ID) Like "01*"));

Example
ID GroupNumber GroupName
010101 01 red
010102 01 red
010201 02 blue
010301 03 green