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. |
#2
|
|||
|
|||
Database (re)design help
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. |
#3
|
|||
|
|||
Database (re)design help
Hello,
Thank you for the insight. Maybe I should approach my question from another angle. If even then nobody wants to touch it, then fine. What I detailed in the original post is how the database looks now. I'm aware it needs redesigning because it's not efficient any more. Most of the data that I use is gained from excel files - that's the only way I can do it -and as far as I'm concerned the whole project can be started from scratch. What I'm at loss about is I don't know which way to start. I feel like I have the right tools in my hand to do this I just don't know how to put these tools to use. Thanks anyway for the reply. Chris "tina" wrote: 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. |
#4
|
|||
|
|||
Database (re)design help
Chris,
For a situation like yours: Step 1 Whether you are doing it for yourself or to post the question to the newsgroup, your first step is to define the nature, structure and relationships of the data that you wish to store, and the mission of what you want to accomplish. You are probably assuming that you have done this by providing info on your current DB design attempt, but you have not. Step 2 Then design a table structure that stores the above data in a way that supports accomplishment of your mission. Step 3 Then design queries, forms and reports that accomplish you mission. Sincerely, Fred |
#5
|
|||
|
|||
Database (re)design help
Chris
To add to the excellent observations you've received so far, I typically advise folks who want to use Access to create an application that they need to plan on spending time coming up to speed on three separate learning curves. 1. Relational database design and normalization 2. Access tricks/techniques 3. User(-friendly) graphical interface design Oh yes, one more ... experience developing software. If you don't think you'll be able to dedicate the time and resources for all of these, consider looking for someone who already has. Good luck! Regards Jeff Boyce Microsoft Office/Access MVP "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. |
#6
|
|||
|
|||
Database (re)design help
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. |
#7
|
|||
|
|||
Database (re)design help
|
#8
|
|||
|
|||
Database (re)design help
"Steve" wrote in message
news Hello Chris, Get lost Steve. OP bewa http://home.tiscali.nl/arracom/whoissteve.html |
#9
|
|||
|
|||
Little stevie is at it again
"Steve" wrote in message
news Hello Chris, I have designed table structures for numerous customers. My fee is very modest. If you would like my help, contact me at . Steve These newsgroups are provided by Microsoft for FREE peer to peer help. Little stevie is well aware of this fact, but continues to prey on unsuspecting posters offering to seperate then from their money. He has proven many times in the past, that what he offers is not help. So now you are attempting to be modest rather than reasonable. You never were reasonable in the past and rather than modest, your proposal is obscene. Stevie, it is time for you to go away AGAIN. John... VIsio MVP |
|
Thread Tools | |
Display Modes | |
|
|