If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Database (re)design help
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. |
Thread Tools | |
Display Modes | |
|
|