recuring schedule or repeating schedule

[MLHab306]

Hi

I am trying to create a recuring schedule. For example client 1 is every 2 weeks, client 2 every 5 weeks, client 3 is every 9 weeks, client 4 is every 4 weeks, etc.
I have several tables one contains the date client was last done another contains the name of the client and interval of appt. I have tried datediff but was getting join errors and I have no idea what to do. So I am starting from scratch with a new query. Any suggestion would be greatly appreciated.

Thanks

Mark

 

[MichaelRed]

UPDATE tblLastAppmt INNER JOIN tblAppnt ON tblLastAppmt.ClientId = tblAppnt.ClientId SET tblLastAppmt.NxtDte = DateAdd("w",[ApntInterval],[LastDte]);


Look at the above SQL statement. I would assume that the table and field names are different, the important issue is:

SET tblLastAppmt.NxtDte = DateAdd("w",[ApntInterval],[LastDte]);


Note that it uses DATEADD, not datediff.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[MLHab306]

I am clueless where the update statement would go. I am a real light weight when it comes to access. I am completely clueless when it comes to coding or sql. I've become pretty good using the wizards, cut and paste and trial and error. Tek is the first real access forum I have been on and I am impressed by the responses. In your response to my thread you wrote:

UPDATE tblLastAppmt INNER JOIN tblAppnt ON tblLastAppmt.ClientId = tblAppnt.ClientId SET tblLastAppmt.NxtDte = DateAdd("w",[ApntInterval],[LastDte]);


Look at the above SQL statement. I would assume that the table and field names are different, the important issue is:

SET tblLastAppmt.NxtDte = DateAdd("w",[ApntInterval],[LastDte]);


Note that it uses DATEADD, not datediff
*****************

My tables and fields are:

Table: Field: type:
customers LastName text
customers CustomerID autonumber primary key
customers IntervalOfAppt number

Date_Last_done Date_Last_done date/time
Date_Last_done CustomerID number related to customers table
Date_Last_done ID autonumber primary key

There are numerous other fields in each table but I only included what I thought was important for this query. I also have 2 or 3 tables not related to anything. If I need to create a new table query etc, just offer suggestions. Where would I place this code? If I open the sql window and there is code there do I overwrite the exisitng code or do I append it and how and what special characters would I need to insert and where. I was also trying to include a screen print of my database relationships but it would not paste.

Thank you in advance for your time and suggestions.


Mark
MLHab3062@aol.com

 

[MichaelRed]

In your tables, I do not see the "Next Appointment". I would expect:

Next Appointment ~~ Last Appointment + Appointment Interval MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[MLHab306]

Thank you for your suggestions and help. Here is what I ended up with after some trial and error and making sure I had a backup before I did anything. .
" UPDATE Customers INNER JOIN Date_last_done ON [Customers].[CustomerID]=[Date_last_done].[Customer_ID] SET Date_Last_done.DateNextAppt = dateadd("d",[customers.IntervalOfAppt],[date_last_done]); "

The only difference between that and this
" datenext: [date_last_done]+[intervalofappt]" is that the previous sql statement updates to the field/column DateNextAppt instead of just in a query datasheet or a report.

*** Is there any way of setting it up to create a whole year or time period vs just the last entry in the Date_last_done field? A loop or ?

Thank you for your time and suggestions.

Mark

 

[MichaelRed]

It is possible to set up multiple appointment dates, however this would require a large change in the schema, a more complex updating procedure and -most importantly- probably not work well from a business perspective. Since there are OFTEN reasons to "re-schedual" individual appointments, you would then need to have additional procedures to cascade the change of one pre-schedualed appointment throughout the existing schedual list. This might need to include a decision on wheather to even do this, or other considerations.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[MLHab306]

I appreciate your recommendations. In my business I am a mobile dog groomer and we work with repetitive scheduling. The clients appreciate getting a calendar with the years appointments on it, that they can post on the refrigerator. Yes I agree people change their schedules but that should be easy to edit in the nextappt field on a case by case situation. When they change their schedule it is usually only a few days or they skip an appointment (giving us a heads up of what is happening). They usually do not request a new schedule based upon the rescheduled appointment. They sometimes adjust the schedule for the holidays or for parties. Plus in the nextappt field I would be entering appointments that are not recuring for non regular customers.

Again Thank You for your time and knowledge of Access.

Mark