FN-FORUM: Displaying tabular data across recordsets.

date posted 10th September 2007 12:06

Hi

The Freelancers site was down when I posted this earlier, so apologies
if you've got it twice.

This follows on from my post last week. I've got the query working
fine (although I'm hoping it can be advanced to make some of the logic
easier). This is what I've got so far:

http://www.perpetual-solutions.com.servwise.com/schedulepdfcategory.asp?id=
=3D100&groupID=3D4

The problem is that some rows should be merged if they have the same
course code, location, and duration. So for example rows 2 & 3 should
be only the same row (to make use of the grid format). What I was
planning was to apply some logic that stored previous row information,
and looked at the next one before writing out the row. I'm already
thinking this could be a fair bit of code as I'll also have to think
about issues like displaying up to 3 dates in the same table cell.

However I've now noticed that row 7 should also be displayed on the
same row as rows 2 & 3. It has the same course, title, and location
but does not follow sequentially (there are rows in between).

So firstly I'm wondering whether there is a way to order my query by
multiple values. I'm already ordering by course code but I'd then
like to order it further. I.E. could I then order it by location?

This is my current SQL =85

sql =3D "SELECT tblCourseCategories.ID, tblCourseCategories.Description,
tblCourseListings.CourseCategoryID, tblCourseListings.CourseID,
tblCourses.ID, tblCourses.DisplayOrder, tblCourses.Title,
tblCourses.Active, tblCourses.CourseCode1, tblCourses.CourseCode2,
tblCourses.CourseCode3, tblCourses.CourseCode4,
tblCourseAvailability.CourseCode AS CourseCode,
tblCourseAvailability.Supplier, tblCourseAvailability.StartDate,
tblCourseAvailability.Location, tblCourseAvailability.Duration,
tblCourseAvailability.RRP FROM ((tblCourses INNER JOIN
tblCourseAvailability ON (tblCourses.CourseCode4 =3D
tblCourseAvailability.CourseCode) OR (tblCourses.CourseCode3 =3D
tblCourseAvailability.CourseCode) OR (tblCourses.CourseCode2 =3D
tblCourseAvailability.CourseCode) OR (tblCourses.CourseCode1 =3D
tblCourseAvailability.CourseCode)) INNER JOIN tblCourseListings ON
tblCourses.ID =3D tblCourseListings.CourseID) INNER JOIN
tblCourseCategories ON tblCourseListings.CourseCategoryID =3D
tblCourseCategories.ID WHERE tblCourseCategories.ID=3D" & strID & "
ORDER BY tblCourses.DisplayOrder;"

Alternatively, am I approaching this completely wrong? I've never
needed to do this before, but imagine it's a fairly common need. Is
there a nice easy way to do it? I've tried googling but don't even
know what I should be searching for.

TIA
Paul