After changing a date in a schedule, increment following dates (1 Viewer)

[taball0829]

I have tblSchedule. If one of the dates does not fit into our schedule it can be changed in the frmSchedule. In the uploaded DB, BowlWeek 18 was Christmas, so I updated it to the following week date. It could be changed to January 8, since January 1 is obviously a holiday, as well. (I know there are ways to skip holidays, but for this discussion it serves a good example -- we may need to skip week 8 because a thunderstorm caused a power outage, or week 23 because there is snow up to our hips.)

My problem: Changing a date to the following week date results in two weeks having the same date, or, as in changing from December 25, to January 8, besides having two weeks with the same date, there is a date prior. (Sequence would be January 8, January 1, January 8.) How do I change dates for the following weeks programmatically? I would rather not export the table to Excel (Where this is easy to fix), fix it and re-import to Access. But, if that is the best practice AND easiest, I would accept that as the answer.

I did a find duplicates query, but do not know how to implement anything with those results. Everything I tried complained that I was using data from a table and a query based on that table.

 

[theDBguy]

Hi. Without looking at your file, you should be able to use an UPDATE query to adjust all future dates to a specified number of days. For example, let's say you have a schedule for today (4/21/2020) that you want to change to next week and also adjust all other future dates to their respective following week. You could try this query:

UPDATE TableName SET ScheduleDate=DateAdd("ww",1,[ScheduleDate]) WHERE ScheduleDate>=Date()

Hope that helps...

 

[Pat Hartman]

I don't like inserting records ahead of time and this is the reason. If the schedule is regular and has rules such as skip a week/month if the scheduled date falls on a holiday, then I would never put more than one future date in the table a time.

 

[theDBguy]

I don't like inserting records ahead of time and this is the reason. If the schedule is regular and has rules such as skip a week/month if the scheduled date falls on a holiday, then I would never put more than one future date in the table a time.

That's actually a sound concept, and I agree with it. I wrote something about it a while back.

Placeholder Records

I have always recommended against "placehoder" records (with possible exceptions), but someone in the forums recently asked me "why?" So, to...

thedbguy.blogspot.com

 

[taball0829]

Hi. Without looking at your file, you should be able to use an UPDATE query to adjust all future dates to a specified number of days. For example, let's say you have a schedule for today (4/21/2020) that you want to change to next week and also adjust all other future dates to their respective following week. You could try this query:

UPDATE TableName SET ScheduleDate=DateAdd("ww",1,[ScheduleDate]) WHERE ScheduleDate>=Date()

Hope that helps...

That is, essentially how I made the schedule in the first place, but I do not see that it accounts for starting somewhere other than the beginning. In the example I gave, I changed the date for week 18.

 

[taball0829]

That's actually a sound concept, and I agree with it. I wrote something about it a while back.

Placeholder Records

I have always recommended against "placehoder" records (with possible exceptions), but someone in the forums recently asked me "why?" So, to...

thedbguy.blogspot.com

I knew it was a mistake to use a holiday date. It has become a red herring.

In the actual use or application of the schedule during data entry, I can see the point of adding records as they occur, but the schedule needs to be published for members of the league to see.

The table with game results does not have all these dates and weeks in it, only the weeks that have had scores entered.

 

[theDBguy]

That is, essentially how I made the schedule in the first place, but I do not see that it accounts for starting somewhere other than the beginning. In the example I gave, I changed the date for week 18.

Where it starts is dictated by the WHERE clause. In my example, it starts today (4/21/2020). In your example, you can maybe say WHERE GameWeek=18. No?

 

[Cronk]

As to not "inserting records ahead of time", how do you have a booking system or scheduling without inserting records ahead of time?

 

[taball0829]

Your input has made me recognize a different problem in my DB which I can now fix!

 

[Pat Hartman]

Booking such as a hotel reservation is a different problem. Those need to be saved. But if they change, they would change one at a time. You might cancel one or move it to a different day but that would not affect future bookings. Bookings are independent. They are usually one-offs but they can be recurring. For example, if you hold a meeting on the First Wednesday of each month at the Hilton, the Hilton needs to add your booking for all dates to some future date so it can avoid double booking the room. But if you change one date, that would not impact other dates in the series.

Schedules are prone to disruptions so a published schedule, i.e. third Tuesday is better. If you have to encumber a venue, that makes this a booking and so whoever is responsible for the venue needs to save the reservation as a record so there is no conflict. My Condo association has monthly meetings. The association doesn't need to create hard records in a table for every one. It just creates a record for the next one. However, the management company who controls usage of the common meeting room DOES need to log our meetings for some time into the future to prevent double-booking.

So, depending on what side of the action you are on, you may or may not need to create actual future records. And in this case, if one meeting change can affect subsequent meetings, then you almost certainly don't want to create the "dummy" records.

To publish a schedule, you would do it with a query and calculate the future dates on the fly. If the schedule changes, you just rerun the query after making the appropriate change.