I will preface this by saying I am a relative novice to Access but I have managed to develop some basic databases. I have some working knowledge of expressions and macros but VBA is still a bit of a mystery to me.
I have a problem that is simple to visualise mentally but seems to be quite a challenge to implement in a database (at least for me :)).
I work in a program which involves calling clients on a daily basis who are typically elderly and living alone who have various health issues which render them potentially vulnerable to sudden adverse health events. We need to improve our management of our daily call roster which involves determining each day whether clients need to be called or not based on two basic conditions:
- Pre-selected regular days the client has established not to be called. (e.g. no call Mon, Wed, Fri etc.)
- Ad-hoc changes to call days (going into hospital, away on holiday from date to date etc.).
I initially started the project by incorporating a multivalue field in the Clients table which makes it easy to select or deselect days, but when it comes to developing an expression to filter the list for any particular day it is proving to be a challenge.
I then considered a "DaysOfWeek" table which could be referenced by a junction table "ClientCallDays". The challenge is in being able to resolve any resulting query to only one result for each client each day.
I am now considering including yes/no fields for each day of the week, which is easy then to deal with in a form or IIF query with OR operators nested (e.g. "If today falls between dateX and dateY or is selected in the Yes/No field corresponding to Today, then don't call, otherwise call") but may violate normalization.
Best Answer
Sounds like you need a proper CRM (: For the days of the week I think you have it right, have a column for each day of the week and if a day is a "no call day" you check it off. Your query would then say, "ok it's Monday who can I call?".
As far as the adhoc days (on vacation etc). You may just want to have a notes field that shows on each record on the call list. This can be reviewed before calling and modified or skipped based on the content of the note.