[thelist] Help with SQL query JOIN using ASP/VBScript

[Pringle, Ron]

Hi all-

I'm using classic ASP/VBScript and querying an Access DB. I'm currently running some queries on different "events" pages where I grab events from an events table and a dates table (plus other tables as well), joining them on a PK field of eventID.

Normally this query populates a calendar of events, so it creates a full record of each event for each date. Now I've been asked to create a list of events for various departments and show it as a list, ordered by date, on the page. No problem, done. However, it looks pretty silly to show the same event title and description over and over again when it has multiple dates, and I think it would be confusing to the end user.

So instead I want to list those events once, with multiple dates attached to them. Each date would then be a link to the full record containing the specific date/time info for that event date.

My current query is below. How do I need to modify this to accomplish what I want? I still have problems wrapping my head around JOINs.

Current Query:

SELECT eventType, eventDate, dateID, title, summary, eventTimeStart FROM ((tbl_event EV LEFT OUTER JOIN tbl_eventDates ED ON EV.eventID = ED.eventID) LEFT OUTER JOIN tbl_contact C ON EV.eventID = C.eventID) LEFT OUTER JOIN tbl_link L ON EV.eventID = L.eventID WHERE EV.deptName = 'Police Department' AND EV.title LIKE '%%"& dynSearch & "%%' AND ED.eventDate >= # " + Replace(currentDate, "'", "''") + " # ORDER BY ED.eventDate ASC, ED.eventTimeStart ASC


TIA,
Ron