earachefl@comca 0 Newbie Poster

I'm trying to join two tables and group the results. The tables are joined on an EventID field. The basic query gets all upcoming events for the following month:

SELECT StartDate, EndDate, StartTime, PresentedBy, EventDescription, AdditionalInfo, VendorEventID, FeaturedText
	FROM EventCalendar, Events
	WHERE EventCalendar.EventID = Events.EventID AND StartDate >= NOW() AND StartDate < DateAdd('d', 31, NOW())

I want to group the events so that all performances of a particular event will be together, and the events will be ordered by date. The problem I'm having is when an event has dis-contiguous performances and a different event falls in the middle of the other event's dates. For example, if there are four consecutive performances:

Vocal concert (Events.EventID 5) 12/1/10
Band concert (Events.EventID 2) 12/2/10
Vocal concert (Events.EventID 5) 12/3/10
Orchestra concert (Events.EventID 3) 12/4/10

I want to group them instead as:

Vocal concert (Events.EventID 5) 12/1/10
Vocal concert (Events.EventID 5) 12/3/10
Band concert (Events.EventID 2) 12/2/10
Orchestra concert (Events.EventID 3) 12/4/10

Note that I can't order them by the EventID, because it's most likely not consecutive.

For the life of me, I can't figure out how I can group them. I know that to use GROUP BY Events.EventID, I have to use an aggregate function, but which, and how? Thanks!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.