[AccessD] A2K: Repeat Appointments

Hi Darren

> I am creating a scheduling dB - Not very well though:-(
> I have 2 Questions (actually many more but we'll start with 2 <vbg>)

> 1)      Can some one point me in the direction about how to create repeat
>         appointment code. EG make the booking below, in Q2, occcur each
> Monday 'til June 30 2003??

You can create some fancy looping code.

However, a somewhat more clever approach is to make a query which
creates the Cartesian product of two tables (two tables with no
joins).
One table contains the bookings, the other is a multiplier table:

<SQL>

SELECT
  tblBooking.ID,
  tblBooking.Interval,
  tblBooking.DateStart,
  tblBooking.DateEnd,
  DateAdd([Interval],[Factor],[DateStart]) AS DateRepeat
FROM
  tblBooking,
  tbzMultiply
WHERE
  (tbzMultiply.Factor <= DateDiff([Interval],[DateStart],[DateEnd])
    AND
  (tblBooking.Interval IN ("h","d","w","m","q"))
ORDER BY
  tblBooking.ID,
  tbzMultiply.Factor;

</SQL>

This requires a table, tbzMultiply, with records from 1 (or 0) to as
many intervals are ever needed, say, 1000.
Interval is a field containing the requested interval, "q", "m", "w",
"d", or "h". Add that field in your booking table. You may wish to
sophisticate this by adding an interval multiplier to allow repeated
bookings for fortnights, half years etc. etc.

> 2)      If a booking is made on Jan 6th 2003 from 1pm to 3pm by John Smith
>         how would I get it to display across 3 time slots or something like
> it.

You can use the above query for this too.

> I am envisioning each of the days mon Tues etc will be a single subform
> within the db
> but placed on the Parent form 7 times - each with a slightly different
> recordsource.

If it is for read-only, I would use a crosstab query and one subform.

/gustav