Help with structure and order of operation. (1 Viewer)

[The_Vincester]

Say I have a table full of tasks. Some tasks are performed each day (Mon-Fri, Friday only, Saturday only, etc). Each day someone has to mark which tasks were not completed, and move those to another table (?). Each task may have small changes to it (length of time, who performs the task, etc.).

There is one person tracking these 350 tasks per day, which they run a report generated from another server that tells them what wasn't completed.

I would like form to be able to show what was completed for the day (by basically subtracting what wasn't completed), as well as for a range of days.

Most of this I can do, but the basic problem is how to generate a "daily" schedule, or a list that he can select what wasn't done? I thought about make a table of days from now until about 2010, and make a query with no relationship so that it assigns a date to each entry (which creates several hundred thousand lines), but that wouldn't do because there can be change that occur for that task for one day or several months.

How does one handle reoccurring events like this? I've gotten fairly good with Access over the past several months, and 90% of what I want to do is no problem, but this one problem, with scheduling, is something I've never been able to figure out. I really need this for 3 separate projects!

The closest I've come is using an append query that pulls a date from a control on a form with a button press, and adds to a list of tasks, but I feel there should be a more elegant (and foolproof) option.

 

[The_Doc_Man]

Recurring scheduling problems are among the nastiest to consider because of the difficulty of managing the dates.

Search this forum for articles on Scheduling.

The way I would approach this is to define a schedule table. In this table, you would have an entry for each task. Part of the entry would include a set of frequency information. This would not be pretty, but you could do it in any of several ways.

tblTaskSched
TaskID, autonumber, PK
TaskDescr, text info about task
other task-related info
TaskFreq: a code that translates via lookup to Daily, Weekly, Monthly, Weekdays, other frequencies.
TaskFrSupp: supplemental data that has different meanings depending on the frequency code. Could be day of the week for weeklies, day of the month for monthlies, ... any of a number of possibilities.
TaskActive: Yes/No - flag that says this task is to be scheduled.

OK, now the above is a list of things you want to do - but NOT the actual list of things for your report. The REAL schedule looks like this:

tblRealSchedule
TaskID, FOREIGN KEY matching TaskID of tblTaskSchedule
SchedOn, today's date
AssignedTo, text
other information you might need
TaskDone, Yes/No

prime key of the Real schedule is the combination of TaskID and Date. This combination must be unique.

(Note: If a task is multiple times per day, you have two choices - more than one task ID or include time with the date.)

Now you write some code to scan this as a recordset. Open a second recordset to append new entries to the REAL schedule table.

Every morning, run a macro that scans the schedule template table, looks at the information in the table, looks at today's information, and decides whether that entry applies TODAY. If so, make an entry in the REAL schedule table with the task's ID number and today's date. Plus any type of assignment data. Set up the macro to run your code and set the TaskDone flag to No.

OK, the issue to consider is that when you have a variation in the day's task, you vary the information in the Real Schedule, not the template. You don't move completed tasks, you just check them off as DONE. Then your queries can key on the state of the TaskDone flag. Done = yes - completed. Done = no - needs more work, not completed.

You can use a JOIN to the schedule template to pick up the descriptive data. Just be careful about what you use for sort keys. The REAL schedule is the one that has REAL dates. The template has frequencies.

This lets you group tasks according to the task ID when appropriate, or the date when appropriate, or the assigned person, or the status, or some combo thereof.

You'll have to develop this a bit and decide whether this makes any sense whatsoever, or whether I have taken a wrong view of your problem. I won't be insulted if it turns out this isn't quite right for you. But maybe it will at least help you think about the problem in a different light.

 

[The_Vincester]

Thank you, I'll try to follow some of your suggestions and apply what you've said. We're thinking along the same lines, as I just finished doing some of what you suggested.

Reoccurring and repetitive tasks are the hardest thing in access I've had to deal with. As you mentioned with running the macro, I've always thought of that as somewhat more "manual" than I would like, but it may be a necessary evil to make the ends meet the means.

I don't mind workarounds for solutions, but this scheduling business seems like "brute force" at times.