Darren Dick
d.dick at uws.edu.au
Thu Feb 13 07:00:00 CST 2003
Hi Stuart Outstanding - this is amazing. I haven't seen that style of query done like that before. This list constantly amazes (and baffles me :0) I now have a super huge add on to the original Q :-) I didn't specify this in the original Q (he says clearing throat, whilst looking at shoes) If a booking starts at 8:00am then with your code and SQL It can be displayed in the appropriate spot. Excellent. Now the tricky bit. How would I show a booking that goes from 8:00am to 11:00am to show up in not just 8:00am slot but also 9:00am and 10:00am slot?? So it is visible to the user that 9 and 10 am are NOT free. (Yes I do capture the end time of the booking) Many many thanks in advance The XTab is brilliant, again many thanks Darren -----Original Message----- From: Stuart McLachlan [mailto:stuart at lexacorp.com.pg] Sent: Saturday, 8 February 2003 4:34 PM To: Darren Dick; accessd at databaseadvisors.com Subject: Re: [AccessD] A2K CrossTab Q On 13 Feb 2003 at 15:41, Darren Dick wrote: > Hello all > I have managed to get a crosstab query working (hooray) That's the good bit. > It is designed to show bookings over a week and also their start times > A Bit like a roster > > 1/1/2003 2/1/2003 3/1/2003 etc > 08:00 Booking No3 > 09:00 Booking No2 > 10:00 Booking No1 > 11:00 Booking No6 > 12:00 > etc > > The problem is... The XTab only shows the dates or time only where a booking > exists. > EG if there are only 4 start times for all the bookings for a week then only > those 4 times appear > on the left hand side. Same goes with the dates. If only 2 dates in the > whole week have > bookings in them then only those 2 dates will appear as column headers. > I know that is what the Crosstab is designed to do, but how can I get the > result to show all times > from say 7:00am to 6:00pm and from 1/1/2003 to 7/1/2003 even though only > some of them will have a value > Assuming your tblBookings contains: BDate (Date/Time) BTime (Date/Time) Comments (Text) Create two extra Tables ================== tblTimes - Contains one Column sTime (Date/Time) tblDates - Contains one Column sDate (Date/Time) Before running the crosstab: ===================== 1. Fill tblTime with a series of records containing the times, 07:00,.08:00 etc (you can probably populate this table once and then leave it there permanently) 2. Clear tblDates and then populate it with all of the dates between the start and end dates. (there are many ways to do this in code) Now all you need is two queries: ========================= Query QPartA (Builds the full set of dates and times): ------------------- SELECT tblDates.sDate, tblTimes.sTime FROM tblDates, tblTimes GROUP BY tblDates.sDate, tblTimes.sTime; Query XTab (Crosstabs the full set of dates and times and inserts any booking info): ------------------- TRANSFORM Max(tblBookings.Comment) AS Booking SELECT qPartA.sTime FROM qPartA LEFT JOIN tblBookings ON (qPartA.sTime = tblBookings.BTime) AND (qPartA.sDate = tblBookings.BDate) GROUP BY qPartA.sTime PIVOT qPartA.sDate; -- Stuart McLachlan Lexacorp Ltd Application Development, IT Consultancy http://www.lexacorp.com.pg