Group By and Order By

[calista]

How do I fix this query so that the results will be grouped by RoomID, and in order by ReserveRoomStart? Here, what I have, and I always have trouble with this kind of query.

Here's the query:

SELECT ReserveID, ReserveRoomStart, ReserveRoomEnd, PersonFirstName, RoomName, PersonLastName
		FROM   ReserveTable, RoomTable, PersonTable 
		WHERE  ReserveRoomID = RoomID AND
	           ReservePersonID = PersonID
			   <CFIF Form.Employee_ID IS NOT &quot;Anyone&quot;>
			   AND PersonID = '#Form.Employee_ID#'
			   </CFIF>
		GROUP BY 	RoomName,
					ReserveRoomStart

And, here's the error:

Error Diagnostic Information
ODBC Error Code = 37000 (Syntax error or access violation)


[Microsoft][ODBC Microsoft Access Driver] You tried to execute a query that does not include the specified expression 'ReserveID' as part of an aggregate function.


SQL = "SELECT ReserveID, ReserveRoomStart, ReserveRoomEnd, PersonFirstName, RoomName, PersonLastName FROM ReserveTable, RoomTable, PersonTable WHERE ReserveRoomID = RoomID AND ReservePersonID = PersonID GROUP BY RoomName, ReserveRoomStart"

Calista :-X
Jedi Knight,
Champion of the Force

 

[tlbroadbent]

All the columns listed in the SELECT statement must be included in an aggregate function. Only two columns are "aggregated" in your SQL statement - RoomName and ReserveRoomStart. You must use aggregate functions for all the columns or remove the GROUP BY clause. You can include the other coumns in the GROUP BY or you can use aggregate functions such as LAST, FIRST, MAX, or MIN.

Another option is to restructure your query to aggregate the two columns in a sub-query and join the other tables to the sub-query. Without knowing the table structures and the exact result you want to see, I can't recommend how to restructure your query beyond these general guidelines. Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[calista]

Thanks! I'll work with that a bit. I've never really understood those aggregate functions. Here's what I'm trying to accomplish. I'm creating an online room reservation system for people to reserve conference rooms in the building. What I am trying to do with this query is when someone wants to view all reservations, the reservations appear grouped by room, and in order by the meeting start time within a given room. My tables are structured as follows:

ReserveTable:
ReservationID (primary key)
ReserveRoomID ID of room being reserved (FK)
ReservePersonID ID of person making reservation
ReserveStart Start time of meeting
ReserveEnd Ending time of meeting

RoomTable:
RoomID (primary key)
RoomName name of conference room

PersonTable contains employee info (name, dept, email, phone, etc.)

Thanks again for your help! BTW, I was off on Fri., so I just got your message. Calista :-X
Jedi Knight,
Champion of the Force