can't get query to group

  • Thread starter Thread starter gator
  • Start date Start date
G

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
 
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*"));
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top