can't get query to group

[gator]

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

 

[Jerry Whittle]

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*"));

 

[gator]

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

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.

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

 

[vanderghast]

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

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

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.

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