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
|
|||
|
|||
Change of address
I am a self-taught novice (the worst kind!) so am hoping I can get a nice
simple answer to my question. First the history: I want to re-build a database I've been using for a couple of years. It records patients and test results for specific infections. One patient can have many tests at the same time which each have a unique number. Currently the patient name, DOB, GP, 1st line of address, along with test details - type of test, date of sample, reported date etc., are all in one table. This means if John Smith has 6 tests on one day I have to enter all of his personal details 6 times, so I am planning to pull that info out to a patient table. As time has gone on extra info has been added. Previously I had no unique identifier for a patient, so could never be sure if John Smith born on 01/01/1950 was the same as John Smith born on 01/11/1950 and it was a typo, or a different person, but we will soon be getting a national identifier which will help with data quality and confidentiality. So, on to my problem: If I have a separate table for patient details how should I deal with a patient moving home or changing doctor (GP)? It is possible that Mr Smith could be in a nursing home for a period of time then go back home, so I need to ensure tests are against the correct address (they could pick up an infection in the home). Should I add a new entry for him detailing the new address and then if he goes back home a 3rd entry which would be the same address as the 1st entry? How can I then make sure that tests are reported against the correct address when I do historical reporting? e.g.: address test ID date 23 Oak Lane 1234 01/04/08 Old folks NH 1254, 4578, 6541 10/06/08 23 Oak Lane 5547 23/09/08 Do I need to give each address a unique ID and pull that into the table containing test ID? |
#2
|
|||
|
|||
Change of address
Kate,
As a side note, your "changing doctor(GP)" note implies that the doctor's name is a part of your mission/question, but you didn't tell us anything about that. Another question: Do your have a requirement for recording a then-current address for each test? Let's assume not. Also, a cornerstone of this will be to have each patient uniquely / correctly identified with a number, and have only one "patient" entry for eadch patient. You have described a path you are taking to that point. Let's assume that you have already accomplished this. The this is going to have to start with you making a decision between Plan "A" and plan"B". "B" is more work; you need to decided if it is necessary or sufficiently useful to retain previous addresses. Plan "A": Just keep a single address in the patient record/table, and keep it current. Plan "B" Make a second address table, and link it's entries to the patient table on a PatientIDNumber field. Include a "CurrentLocation" field where entery of a ""Y" (Yes) indicatres that that is their current address. Hope that helps a little. Fred |
#3
|
|||
|
|||
Change of address
On 31/10/2008 in message
KateB wrote: Do I need to give each address a unique ID and pull that into the table containing test ID? What about an address table with 'From' and 'To' in it linked to the CustomerID, you could then pull the address that was valid on a specific date. If you go that route I would be inclined to use a date index (number of days from a global starting date) in an integer field as it is quicker/easier to search on than DateTime (in my experience anyway). -- Jeff Gaines Damerham Hampshire UK There is no reason anyone would want a computer in their home. (Ken Olson, president Digital Equipment, 1977) |
#4
|
|||
|
|||
Change of address
Thanks Fred, I have answered each part below:
"Fred" wrote: Kate, As a side note, your "changing doctor(GP)" note implies that the doctor's name is a part of your mission/question, but you didn't tell us anything about that. As with address, I need to know who their family doctor was at the time of the test as the causes of the infection could relate to treatment given. Therefore I thought this could be done in a similar way - or even in the same table?! Another question: Do your have a requirement for recording a then-current address for each test? Let's assume not. I need the address at the time of the test - if they were in a nursing home there could've been an outbreak and that info would be lost if the patient then went back home and the address overwritten. Also, a cornerstone of this will be to have each patient uniquely / correctly identified with a number, and have only one "patient" entry for eadch patient. You have described a path you are taking to that point. Let's assume that you have already accomplished this. The this is going to have to start with you making a decision between Plan "A" and plan"B". "B" is more work; you need to decided if it is necessary or sufficiently useful to retain previous addresses. Going to have to go with something along the lines of Plan B! Plan "A": Just keep a single address in the patient record/table, and keep it current. Plan "B" Make a second address table, and link it's entries to the patient table on a PatientIDNumber field. Include a "CurrentLocation" field where entery of a ""Y" (Yes) indicatres that that is their current address. Hope that helps a little. Fred |
#5
|
|||
|
|||
Change of address
Thanks Jeff,
This is exactly my problem. I can record all the addresses, tests, patients, etc, but am not sure how to tie the address to the correct test in queries at a later time. I think a combination of yours and Fred's suggestions may work for me. Lots of trial and error to come! Kate "Jeff Gaines" wrote: On 31/10/2008 in message KateB wrote: Do I need to give each address a unique ID and pull that into the table containing test ID? What about an address table with 'From' and 'To' in it linked to the CustomerID, you could then pull the address that was valid on a specific date. If you go that route I would be inclined to use a date index (number of days from a global starting date) in an integer field as it is quicker/easier to search on than DateTime (in my experience anyway). -- Jeff Gaines Damerham Hampshire UK There is no reason anyone would want a computer in their home. (Ken Olson, president Digital Equipment, 1977) |
#6
|
|||
|
|||
Change of address
Kate,
Here is how I think you should structure it. In that we have two moving targets, address and GP and that you need to know both pieces specific to a test, and the each test could have either or both of those items different for the previous test. PatientIdentification. Include name of patient and those things about the patient that will not change like Birthday. Don't use the name as part of the primary key, because that can actually change. Include in ths table a foreign key to the PatientDemographics and Doctor tables so you know what the current items are. In your application you can change this when a patient is moved. PatientDemographics. Addresses, phone numbers, email addresses, etc. This should have the data items that can change and may be specific to a test. DoctorTable. The information about the doctores. Test Table. This would have relations to the PatientIdentification, PatientDemographics, and Doctor Tables as well as information about ONE and ONLY ONE test. If multiple tests are done on the same day, you would have a record for each test. And, in your application when you set up a test record, you need to construct your form so it will show the current demograhpic and doctor information but allow the user to use an exsiting address or doctor or create a new address or doctor. "KateB" wrote in message ... Thanks Jeff, This is exactly my problem. I can record all the addresses, tests, patients, etc, but am not sure how to tie the address to the correct test in queries at a later time. I think a combination of yours and Fred's suggestions may work for me. Lots of trial and error to come! Kate "Jeff Gaines" wrote: On 31/10/2008 in message KateB wrote: Do I need to give each address a unique ID and pull that into the table containing test ID? What about an address table with 'From' and 'To' in it linked to the CustomerID, you could then pull the address that was valid on a specific date. If you go that route I would be inclined to use a date index (number of days from a global starting date) in an integer field as it is quicker/easier to search on than DateTime (in my experience anyway). -- Jeff Gaines Damerham Hampshire UK There is no reason anyone would want a computer in their home. (Ken Olson, president Digital Equipment, 1977) |
#7
|
|||
|
|||
Change of address
On 31/10/2008 in message
KateB wrote: This is exactly my problem. I can record all the addresses, tests, patients, etc, but am not sure how to tie the address to the correct test in queries at a later time. I think a combination of yours and Fred's suggestions may work for me. Lots of trial and error to come! Perhaps something like: SELECT * FROM tblAddresses WHERE PatientID = RequiredPatientID AND AddressFromIndex = RequiredDateIndex AND AddressToIndex = RequiredDateIndex Ohmigod I didn't say SELECT * did I? Could be fun, hope you've got time to experiment :-) -- Jeff Gaines Damerham Hampshire UK "Why is it that when we talk to God we're said to be praying, but when God talks to us we're schizophrenic?" |
#8
|
|||
|
|||
Change of address
Kate,
These guys know Access design better than I do. My strength is on the implementation and organization of information side. Just one "30,000' view" note to add. In your 2nd post you in essence said that, for each test, your need to (amongst other things) record: - The name or description of the test - The patient's address at the time f the test - The Patient's GP at the time of the test. This means that the above 3 items are fundamentally a part of the test record. How you get them into / store them in the record can be any of several ways such as: - Have theire most recent test record loaded as a default and then editing it. - make separate tables for any or all of the above three items, and use a dropdown list to load the text* - make separate tables for any or all of the above three items, and use a dropdown list to load the ID# of the entry, and link to that table to show the text.* Or a combination of the above. * For address, filter the dropdown list to show addresses associated with the patient. Sincerely, Fred |
Thread Tools | |
Display Modes | |
|
|