take a look at http://home.att.net/~california.db/tips.html, focusing first
on Tips 1 and 2.

hth


"tina" wrote in message
...
honestly, Chris, i don't think anyone in the newsgroups wants to touch
this
one with a ten-foot pole; it has "headache" written all over it. from the
scanty description of the tables, it's pretty clear that the tables are
not
normalized, and trying to work within the current structure will be just
miserable - especially remotely, in a newsgroup forum. since it sounds
like
the data is important to you, i suggest you look for a local developer in
your area to analyze your business process related to this task, build a
new
database to properly house existing and future data, and write a new
application to streamline your day-to-day workflow related to the
database.

if you want to tackle it yourself, then i very strongly recommend that you
begin by putting in the time and effort to learn relational design
principles well. next, analyze your own process carefully. then, and only
then, design a new database and user interface based on a sound relational
structure.

hth


"Chris" wrote in message
...
Hello,

I started a small database many years ago in access that kept track of
some
patient details - I work in a GP surgery - and their recalls. This has
grown
itself out to holding certain data and results of all patients and
keeping
track of their recalls. Doesn't sound very different but I think the old
structure is very cumbersome to work with nowadays.

What I have:
A MainTable that holds patient details (contacts, test results, some
other
bits of info). All the details get updated every month with the latest
information through append and update queries from imported tables.

An InvitationsTable that holds the PatientID from MainTable and the
following fields:
TestID - A lookup field where I enter the name of the test the patient
needs
to be sent for
SendIn - the number of the month when the patient needs to be sent
CheckIn - the number of the month when I check whether the patient has
attended the test (generally the month following SendIn)
1stSent
2ndSent
3rdSent
4thSent
LastSent - these hold the number of the month when the patient was sent
an
invitation. And this is my headache. When this setup was introduced the
idea
was that I remove patients from the InvitationsTable after 3
non-attendance
but some time ago the policy has changed. In the LastSent now I just
enter
the month I'm sending the patient but this way loads of data may get
lost
if
the patient is a particularly lazy one, and we have quite a few of that.
Also
my left thumb is tired of going to the latest Sent field. I'm really
hoping
there is an easier way to do this.

What I need is a setup which supports an easy-entry form for invitations
but
also makes it possible for me to query if a patient has more than 3
invitations in the previous 3 months, so we can recall them.

I have had discussions on this already but no conclusion. One suggestion
was
to keep a record of all invitations ever done - at present I delete the
invitations data if the patient attended or has been excluded from being
invited.

I have tried one-to-many relationships with three tables - one for
data,
one for date and one for the name of the bloodtests, but in form view it
has
not been workable at all.

I am moving on to Access 2007 from 2003, am not a total beginner and
quite
comfortably find my way around tables, queries and forms, but I do find
some
of the more complex processes... well, a bit complex. Thank you very
much
for
your help if any.