Complex report, help

I am writing a program in VB6 as a front end to an Access 97 db reporting with CR 4.6, to recall chronic disease patients to my small health clinic in an Aboriginal area in outback Australia.

There are nine diseases, and patients need to be recalled at different frequencies, for different tests. All up there are 13 discrete ‘things to do’ that they need to be recalled for – e.g. blood pressure, weight, different blood tests.

The ideal outcome of the program is a report, spat out each month (the recall system works in blocks of months) saying who needs to come to the clinic, and what needs to be done for them. This is not as easy as it sounds, as there can be 95 combinations of the nine diseases (some are mutually exclusive – different degrees of kidney disease), each with their own ‘things to do’ at different frequencies. Programs of this kind have typically treated each patient-disease pair as separate entities if a patient has more than one disease, meaning a report will show that patient more than once, and often the ‘things to do’ will have some overlap. These different reports then need to be collated manually. I would like to avoid that, unless it’s a complete programming nightmare.

The other point is that the disease list, and the ‘things to do’, and the frequencies at which they’re done, will change as time goes by, so it would be handy if it was easy to update this aspect of the program.

I’m just playing around with ideas at the moment, but the way I see it there are three main ways of doing this: i) code the whole thing in VB, for each of the 95 combinations of diseases, ii) do something (??) with queries in Access, or iii) do something (??) with CR – although 4.6 is a bit limited.

I'm experienced with sql, if anyone thinks there are possibilities there.

TIA, Peter