Design suggestions requested

[Chris]

Hello there,

I work in a GP surgery, using XP and office 2003. I've been developing an
access database for many years now and it has had several different versions
due to changing demands. The time has come to review its functionality again
and while I'm confident with the basics and know my way around a lot of
things - apart from coding - I've come to a crossroads.

As I said, I'm confident with the basics but I not so much so with pretty
advanced things and am a bit stuck for ideas.

Now for the problem.

I have a database into which I have to import certain data regularly (every
month) and I use this data to send out test invitation letters. The situation
is that I have varyin amount of tests to send out for each patient but even
if I sent a patient for say, testA one month, I might need to send him for
testB next month.
I'd like to keep a log of at least the last 12 months for a patient about
which test I sent them for.

I had two ideas so far. The one I'm using at present in its form version is
that I enter the name of the test, and in the subsequent text fields enter
the number of the months I sent him in. This worked until the idea was that
after 3 invitations they were stopped being sent invitations. Now I have to
keep a continuous record but the two ideas I had would create a humungous
amount of fields. Here they are:

1. Have a new line for each test and use 12 checkboxes after it to tick the
relevant months. A bit of a glitch with this one is that not everone gets
sent first in, say, January and if I am still sending them in 12 months what
am I going to do?

2. Have a new line for each month and create several entries for the tests
sent in those months. Sounds somewhat not practical to me but I couldn't come
up with better.

You see, every month I have to go through several hundred of these patients.
If someone has gone for a certain blood test they will be cleared of that
recall the next month and their previous records regarding that test will be
unnecessary. I still need to be able to create a query to be able to find out
which patients have been sent 4 or more invites. I would still need to be
able to check each month which patients I sent the previous month and be able
to check each month which patient I'm sending that given month. At present I
can create these last two queries by using a sendin and a checkin field in
addition to the previously mentioned ones.

So I feel I'm typing an awful lot unnecessarily and would be grateful for
any ideas that could take me forward from this - and hopefully don't involve
coding. I hope I was clear, I tried to be but if you have any further
questions I'll do my best to explain. Thanks in advance.

 

[Fred]

This is a sort of CRM application with the simplification that you are
dealign with individuals rather than people at companies/organizations.
I've done a lot of that in real life on DB's for 18 years; other people that
respond here know the code side far better than me.

Despite your excellent explanation, after one read there are some details I
don't understand. My gut feel (and add any necessary legal constraints) is
that your underlying structure should be two main tables "People" (PK =
PersonID) and "ActionsEvents" (with a PersonID field) and link them on
person ID. Make up whatever fields you need in each. TheActionEvents
constains things that need to/ you have decided to get done and things that
have been done. Put in fields for "action dates", a description field that
has the main relevant informaiton, and a Status field which differentiates
"to do" vs "done" things.

I think that this would provide a good foundation for doing all of the
things which you describe.

Fred

 

[Klatuu]

All your problems will be solved if you use good relational database design.
You need one table that has PatientID, TestId, Date sent, and DateTested. Add
a record to this table each time an invitation is sent. Update the
DateTested field when the patient shows up for the test. Then you can use
queries or domain aggregate functions on this table to find out anything you
need to know about whether they need a recall or how many invitations you
have sent, etc.