problem with grouping records

[gcapp]

I'm having a problem getting my results to group. I've read and read and I'm just stuck on this.

In summary, I have two tables:

DepartmentID
ID
DeptName

PhoneNumbers
ID
DeptID ->DepartmentID.ID
Listing
Location
Phone
Extension

I have an asp page where I have a drop-down menu that lists the first letter of everything from the DepartmentID.DeptName field. So there is A,B,C, etc.. in there. The form's name is "phone".

So after choosing a letter, I want the results on another page that will list, say the results from letter A, like this:

Administration
Little Valley Office - 373-1234
Olean Office - 372-5678

Attorney
District Attorney - 432-9876

So I tried to come up with an sql statement but i keep getting errors and I just don't know what is wrong. Here is the statement:

sqlphone="SELECT DepartmentID.ID, DepartmentID.DeptName, PhoneNumbers.Listing, PhoneNumbers.Location, PhoneNumbers.Phone, PhoneNumbers.Extension " &_
"FROM DepartmentID INNER JOIN PhoneNumbers ON DepartmentID.ID = PhoneNumbers.DeptID " &_
"WHERE DepartmentID.DeptName LIKE '" & Request.Form("phone") & "%' GROUP BY DeptName"

I keep getting an error stating:
You tried to execute a query that does not include the specified expression 'ID' as part of an aggregate function.


I just don't get what is wrong. I've read up on this and still am stuck. If anyone can offer a solution, it would really help me.

Thanks
Gary

[whammy]

Try:

GROUP BY DepartmentID.DeptName, DepartmentID.ID

Hope that helps, I'm much better at this when I can see what's going on first hand. ;)

Generally this kind of error happens when SQL is not sure which database you're referring to... and you have include which database in the "GROUP BY" statement, either with the full db name or its alias that you have specified.

If that doesn't solve the problem, make sure you are using the actual names of your databases... hope the helps...