FW: [AccessD] A2K CrossTab Q

Hi Darren

> 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)

One option is to create a query that generates all your 168 available
time slots for a week from today.
Again you can use a Cartesian (multiplying) query for this having the
table tbzMultiply (one column with integers) mentioned in my previous
post: 

<SQL>

SELECT
  DateAdd("h",[aliHour]![Factor]-1,DateAdd("d",[aliDay].[Factor]-1,Date()))
    AS Slot168
FROM
  tbzMultiply AS aliDay,
  tbzMultiply AS aliHour
WHERE
  ((aliDay.Factor Between 1 And 7)
    AND
  (aliHour.Factor Between 1 And 24));

</SQL>

Save this as qdyBookingSlots.
Name the query from my previous post selecting the bookings:
  qdy BookingApp

Now, create a crosstab query joining these:

<SQL>

TRANSFORM
  First(qdyBookingApp.ID) AS FørsteOfID
SELECT
  Format([Slot168],"hh:nn") AS Hours
FROM
  qdyBookingSlots
LEFT JOIN
  qdyBookingApp
  ON
  qdyBookingSlots.Slot168 = qdyBookingApp.DatePeriod
GROUP BY
  Format([Slot168],"hh:nn")
PIVOT
  Format([Slot168],"yyyy/mm/dd");

</SQL>

You amy save this as qdyBookingSlotsUsed.

This will output a 7 x 24 grid with showing an ID for those time slots
where a booking exists.
Note the sorting. If you format the date as dd/mm/yyyy you'll get into
trouble for grids crossing a month end.

/gustav