How can you put a dayshedule in a table, what I mean is that for every day in a year you have to be able to put a value and show it on screen, there also has to be a a clear distinguish between the different days of a week, I am in a search for the most efficient solution for this problem




Dries.

You have hit precisely on one of the nastiest problems within Access. Normally, a schedule such as you describe would be "Sparse" - i.e. thinly / incompletely populated. Your schedule would have gaps not adequately represented in any "normal" output report or form.

One approach that you might try is to do a trick with a JOIN query.

As a one-time-only action, build essentially a constant table that contains 365 days for your schedule. Break the days up into day/month pairs. If time is an issue, this table has to include hour and minute, too. You can weed out anything for days when you aren't at work. As to how to do this, it might be easiest to write some VBA code with a few nested loops that create a new record for each day in your calendar.

Now build the REAL schedule table for which you have day, month, time, action / event, etc. If time is an issue, include the hour and minute of the event. Make an entry for any place where you have something to do. But this table can / should be sparse - i.e. do not define a record when there is nothing to do in that corresponding time.

Suppose, for example, you only allow events every 15 minutes on the quarter hour for the hours 8 to 5, excluding a lunch hour at noon. You would have to generate an event for times 8:00, 8:15, 8:30, etc. Only build as many event times as you are willing to support. Leave out holidays, weekends, nights where you don't have second and/or third shift activities, etc. This might represent a little over 8000 slots in a year.

Now build a query for which you can JOIN this table over day/month, selecting to take ALL records from the "constant" table and "any matching records" from the real schedule table. If the time is also a factor, include hour and minute as part of the JOIN. Remember that a query can express a relationship locally that is not a permanent part of the associated tables.

OK, now the tricky part is populating your report with this stuff. The JOINed query will have nulls in fields where a matching JOIN could not be found in the schedule table. So the JOIN query has to use Nz([event],"") or something like that to show empty slots that don't contain nulls. Otherwise you are going to see s***pot loads of errors in the final report.

I don't know if you have enough room, but you MIGHT be able to put as much as a whole week on a page using 7 very narrow columns with appropriate column headers. Just a random thought as to how you MIGHT try this.