FW: [AccessD] A2K CrossTab Q

Hi Gustav
I had Stuart (from this list) reply also - H also came up with an elegant
XTab solution.
I asked the question with his XTab solution in mind. It has essentially
given me 80% of
what I need - Was hoping to get the extra hour fills using his design.

I have been playing with your solution for 2 days and I can't work it out,
(Sorry)
It seems to be giving odd results.
EG if I put in a booking into tbBooking with an Interval of "WW" with
DateStart at 1/1/2003 and
DateEnd 31/01/2003, With the tbzMultiply table going from 1 to 5 (Max no of
weeks in a month)
then I get the expected results. (8th, 15th, 22nd and 29th Jan) - Excellent

If I change the multiplier to say 26 and change the DateEnd to 30/06/2003 -
I would
expect to see every Saturday between January 1st and June 30th 2003. But I
get results
that don't make sense. All of Feb is omitted for example and one would
expect there
to be around 21 - 23 occurrences of each Saturday in that 6 month range
(1/1/2003 Being a Saturday)
I actually get 19 (22nd of Jan is missing, all Feb Dates are missing 1st
March is missing
Then the next dates for March are 12,19th 26th (They're all Wednesdays)
Every date from there on is OK except they are all Wednesdays not Saturdays.

I actually trust your code to be correct, so it must be me.
I haven't understood a concept perhaps, or am playing with the multiplier
table
in the wrong way perhaps.

Again my many thanks for your persistence
And many thanks to you Stuart for your Suggestions

Darren






-----Original Message-----
From: accessd-admin at databaseadvisors.com
[mailto:accessd-admin at databaseadvisors.com]On Behalf Of Gustav Brock
Sent: Friday, 14 February 2003 3:28 AM
To: Darren Dick
Subject: Re: 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

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com