[AccessD] A2K: Repeat Appointments

Hi Gustav
Many thanks for the reply.
This is over my head but I can certainly see the potential.
I have modified my tables as per your SQL but when I copy and
paste the SQL into the QueryDesignGrid I am getting errors related to
the IN function

Like I said - way beyond me <vbg>

Any suggestions??

Many thanks

Gustav, I'm not the sharpest knife in the drawer so this may drag on
for a while, so thanks for your patience in advance :-)

Darren




-----Original Message-----
From: accessd-admin at databaseadvisors.com
[mailto:accessd-admin at databaseadvisors.com]On Behalf Of Gustav Brock
Sent: Monday, 10 February 2003 10:06 PM
To: Darren Dick
Subject: Re: [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

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