4

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:

  1. Pre-selected regular days the client has established not to be called. (e.g. no call Mon, Wed, Fri etc.)
  2. 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.

0

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.

  • As far as "best setup" goes again I'd go with a CRM product like SugarCRM. BUT otherwise my next choice would be to go with something like MySQL. Honestly though if technical resources are tight and your list isn't HUGE you really could just do this in an Excel sheet (gasp). – Matt D Jul 30 '15 at 16:00
  • Another CRM is not an option. This should not be such a difficulty in Access. The application I am dealing with must be not that uncommon. Think of a newspaper delivery application where customers have newspapers delivered some days and not others, or a sales round management situation where some customers get calls on regular days and not other days etc. – stevekos07 Jul 31 '15 at 1:16
0

Generally speaking, I find it less confusing to use positive terms in design. Here it would mean storing "can call" days rather than "no call" days. Up to you, of course, but it may be worth considering.

If you were to normalise fully you would have a Person table and a Day table with values "Monday", "Tuesday" .. "Sunday" and a Callable table with values (Andy, Monday), (Andy, Tuesday), (Bobbie, Tuesday), (Bobbie, Friday), for example. I think the risk of new weekdays appearing is sufficiently small that you can denormalise the days into Person withont worrying.

Going that route, however, complicates your query as you have to compare each "day" column explicity against today's date. If you use the fully normalised form you can employ the EXISTS syntax in a generic way. Your query then becomes (in pseudo code, sorry I don't have a copy of Access to test with)

select Distinct
   Person.Name
from Person
where exists
  (select ..
   from Callable
   where Callable.PersonName = Person.Name
   and Callable.Day = DayName(<your date here>)
and not exists
  (selcet ..
   from Holiday
   where Holiday.StartDate <= <your date here>
   and Holiday.EndDate >= <your date here>
   and Holiday.PersonName = Person.Name)
  • 1
    Thanks Michael. Yes I think that having a discreet table for Days would be better. I have a long way to go in learning Access and coding is not yet in my skillset. I thought that having 7 unique columns which cannot realistically change would not violate normalisation too much, and is easy for me with my level of knowledge to work with. I can set up a separate query for each day and call it qryMondayCalls etc. I can then set up a command button on a form with a macro to run which ever query corresponds to today's Weekday. This might be basic, but if it works I will be happy! – stevekos07 Jul 31 '15 at 11:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.