Re: FN-FORUM: Displaying tabular data across recordsets.

date posted 10th September 2007 12:16

Oh, and you want to do a second category of "Order By" for the =20
location, so your order by clause at the end will read...

ORDER BY tblCourses.DisplayOrder, tblCourseAvailability.Location ASC

Sorry, forgot that in the earlier response!



Quoting Paul Cooper [EMAIL REMOVED]

>
> 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
>
> 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
>
> --
> Freelancers, contractors earn more with Prosperity4
> Call 0870 870 4414 or visit www.prosperity4.com
> and benefit from Inland Revenue approved expenses today.
>
> To advertise here: http://www.freelancers.net/advertising.html
>
>