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
|
|||
|
|||
Serial one-to-many tables
I need help with design of a database I am writing to keep track of
procedures that I do on patients. For each patient, one or more procedures can be done. For each procedure, one or more locations can be sampled (I want to keep track of the sequence of locations sampled, i.e. 1, loc2R; 2, loc2L; 3, loc7; etc). For each location, one or more samples (pass1, pass2, pass3, etc.) can be taken. For each sample there is a result (pos, neg, non-diagnostic). I've come up with this series of tables: tblNames NameID LastName FirstName tblProcedure ProcedureID NameID ProcedureDate ProcedureType tblLocation LocationID ProcedureID LocationSequence LocationName tblResult ResultID LocationID PassNumber PassResult I keep getting lost trying to create a form or sequence of forms that will enable me to enter all the data. It seems that I need a subform (Location:Result) inside a subform (ProceduLocation) inside another subform (Name:Procedure). On the highest level form (Name) I want to be able to select from the existing names (shown as LastName & ", " & FirstName) or enter a new name. Any help with fixing my database design would be much appreciated. Thanks. |
#2
|
|||
|
|||
Serial one-to-many tables
well, you're on the right track. i'd add a few supporting tables, and
rearrange a couple of the others a bit, as tblPatients PatientID (primary key) LastName FirstName okay so far, except note that i changed the table name and primary key fieldname, to be more descriptive. keep in mind that Access is not a secure repository for sensitive data such as social security numbers; if you need to keep that sort of data, you should look at using a server-based solution such as SQLServer. also, your use of the word patients indicates human medical data. if that's correct, and if you're in the USA, your data storage/handling solution needs to be HIIPA-compliant; again, data security is an issue here. tblProcedures ProcedureID (pk) ProcedureName this would be a list of all procedures that you can perform, and nothing else. do NOT include any fields that indicate a specific person or a specific performance of a procedure. tblLocations LocationID (pk) LocationName this would be a list of all locations where an instance of a procedure may be performed, and nothing else. tblSamples SampleID (pk) SampleDescription this would be a list of all possible sample descriptions - or names, if that makes more sense to you - such as pass1, pass2, pass3, etc. and nothing else. tblResults ResultID (pk) ResultDescription this would be a list of all possible result descriptions - or names - such as pos, neg, non-diagnostic, etc. and nothing else. tblPatientProcedures PatProcID (pk) PatientID (foreign key from tblPatients) ProcedureID (fk from tblProcedures) ProcedureDate the table relationships a tblPatients.PatientID 1:n tblPatientProcedures.PatientID tblProcedures.ProcedureID 1:n tblPatientProcedures.ProcedureID make sure you set the relationships in the Relationships window, and enforce referential integrity. tblPatProcLocations ProcLocID (pk) PatProcID (fk from tblPatientProcedures) LocationID (fk from tblLocations) Sequence the relationship is: tblPatientProcedures.PatProcID 1:n tblPatProcLocations.PatProcID tblLocations.LocationID 1:n tblPatProcLocations.LocationID ditto above re setting relationships. tblLocSamples LocSamID (pk) ProcLocID (fk from tblPatProcLocations) SampleID (fk from tblSamples) ResultID (fk from tblResults) the relationships would be: tblPatProcLocations.ProcLocID 1:n tblLocSamples.ProcLocID tblSamples.SampleID 1:n tblLocSamples.SampleID tblResults.ResultID 1:n tblLocSamples.ResultID exactly how you set up your data entry form is determined partly by the work flow, partly by the media that you'll be entering data from (i'm assuming a sheet of paper with data organized in a particular order), and partly on your skill level. the work flow you indicated below; and your skill level can be increased to meet your development needs - trust me, it can the source media we can't see, so you'll have to use your best judgment on making the form suit your needs there. i might start with a mainform, bound to tblPatients. probably an unbound combobox control in the form's Header section, RowSource: tblPatients, to select a particular patient record. perhaps a tab control, with the patient fields on the first page. then a subform on the second page of the tab control, bound to tblPatientProcedures, SingleForm view, with a combobox control bound to field ProcedureID, RowSource: tblProcedures, to pick a procedure when adding a new record and display the procedure name of existing records. then a 2nd level subform, bound to tblPatProcLocations, with a combobox control bound to LocationID, RowSource: tblLocations, again to pick or display a location. if i were using A2000 - A2003, i'd set the DefaultView of the level2 subform to Datasheet. then i'd add a level3 subform, bound to tblLocSamples, DefaultView also as Datasheet, with a combobox control bound to SampleID, RowSource: tblSamples, and another combobox bound to ResultID, RowSource: tblResults. (if you're using A2007, i don't know what options may be available; if you're using A97, a different method is needed - but let's not go there unless we have to.) so you open the form, add a patient record or find an existing one in the combobox in the form Header. click on the 2nd tab, and add or go to a procedure record (you can use an combobox here, too - or a listbox - to find a specific procedure record for the selected patient). then enter or go to a location record in the datasheet, and enter or edit sample records in the subdatasheet of each location record. hth "CuriousMark" wrote in message ... I need help with design of a database I am writing to keep track of procedures that I do on patients. For each patient, one or more procedures can be done. For each procedure, one or more locations can be sampled (I want to keep track of the sequence of locations sampled, i.e. 1, loc2R; 2, loc2L; 3, loc7; etc). For each location, one or more samples (pass1, pass2, pass3, etc.) can be taken. For each sample there is a result (pos, neg, non-diagnostic). I keep getting lost trying to create a form or sequence of forms that will enable me to enter all the data. It seems that I need a subform (Location:Result) inside a subform (ProceduLocation) inside another subform (Name:Procedure). On the highest level form (Name) I want to be able to select from the existing names (shown as LastName & ", " & FirstName) or enter a new name. Any help with fixing my database design would be much appreciated. Thanks. |
#3
|
|||
|
|||
Serial one-to-many tables
TblPatient
PatientID FirstName LastName TblProcedure ProcedureID Procedure TblLocation LocationID Location TblPatientProcedure PatientProcedureID PatientID ProcedureID TblPatientProcedureLocation PatientProcedureLocationID PatientProcedureID LocationID TblSampleResultID SampleResultID SampleResult (Pos, Neg, Non-Diagnostic) TblPatientProcedureLocationSample PatientProcedureLocationSampleID PatientProcedureLocationID SampleName SampleResultID Steve "CuriousMark" wrote in message ... I need help with design of a database I am writing to keep track of procedures that I do on patients. For each patient, one or more procedures can be done. For each procedure, one or more locations can be sampled (I want to keep track of the sequence of locations sampled, i.e. 1, loc2R; 2, loc2L; 3, loc7; etc). For each location, one or more samples (pass1, pass2, pass3, etc.) can be taken. For each sample there is a result (pos, neg, non-diagnostic). I've come up with this series of tables: tblNames NameID LastName FirstName tblProcedure ProcedureID NameID ProcedureDate ProcedureType tblLocation LocationID ProcedureID LocationSequence LocationName tblResult ResultID LocationID PassNumber PassResult I keep getting lost trying to create a form or sequence of forms that will enable me to enter all the data. It seems that I need a subform (Location:Result) inside a subform (ProceduLocation) inside another subform (Name:Procedure). On the highest level form (Name) I want to be able to select from the existing names (shown as LastName & ", " & FirstName) or enter a new name. Any help with fixing my database design would be much appreciated. Thanks. |
#4
|
|||
|
|||
Serial one-to-many tables
Wow. Thanks very much tina. I'll have to print this out and work through it a
bit. I'm not exactly a novice at Access, but not experienced enough to understand your answer on the first read . Thanks also for the advice on HIPAA compliance. It is an issue and has been taken into account on several levels. "tina" wrote: well, you're on the right track. i'd add a few supporting tables, and rearrange a couple of the others a bit, as tblPatients PatientID (primary key) LastName FirstName okay so far, except note that i changed the table name and primary key fieldname, to be more descriptive. keep in mind that Access is not a secure repository for sensitive data such as social security numbers; if you need to keep that sort of data, you should look at using a server-based solution such as SQLServer. also, your use of the word patients indicates human medical data. if that's correct, and if you're in the USA, your data storage/handling solution needs to be HIIPA-compliant; again, data security is an issue here. tblProcedures ProcedureID (pk) ProcedureName this would be a list of all procedures that you can perform, and nothing else. do NOT include any fields that indicate a specific person or a specific performance of a procedure. tblLocations LocationID (pk) LocationName this would be a list of all locations where an instance of a procedure may be performed, and nothing else. tblSamples SampleID (pk) SampleDescription this would be a list of all possible sample descriptions - or names, if that makes more sense to you - such as pass1, pass2, pass3, etc. and nothing else. tblResults ResultID (pk) ResultDescription this would be a list of all possible result descriptions - or names - such as pos, neg, non-diagnostic, etc. and nothing else. tblPatientProcedures PatProcID (pk) PatientID (foreign key from tblPatients) ProcedureID (fk from tblProcedures) ProcedureDate the table relationships a tblPatients.PatientID 1:n tblPatientProcedures.PatientID tblProcedures.ProcedureID 1:n tblPatientProcedures.ProcedureID make sure you set the relationships in the Relationships window, and enforce referential integrity. tblPatProcLocations ProcLocID (pk) PatProcID (fk from tblPatientProcedures) LocationID (fk from tblLocations) Sequence the relationship is: tblPatientProcedures.PatProcID 1:n tblPatProcLocations.PatProcID tblLocations.LocationID 1:n tblPatProcLocations.LocationID ditto above re setting relationships. tblLocSamples LocSamID (pk) ProcLocID (fk from tblPatProcLocations) SampleID (fk from tblSamples) ResultID (fk from tblResults) the relationships would be: tblPatProcLocations.ProcLocID 1:n tblLocSamples.ProcLocID tblSamples.SampleID 1:n tblLocSamples.SampleID tblResults.ResultID 1:n tblLocSamples.ResultID exactly how you set up your data entry form is determined partly by the work flow, partly by the media that you'll be entering data from (i'm assuming a sheet of paper with data organized in a particular order), and partly on your skill level. the work flow you indicated below; and your skill level can be increased to meet your development needs - trust me, it can the source media we can't see, so you'll have to use your best judgment on making the form suit your needs there. i might start with a mainform, bound to tblPatients. probably an unbound combobox control in the form's Header section, RowSource: tblPatients, to select a particular patient record. perhaps a tab control, with the patient fields on the first page. then a subform on the second page of the tab control, bound to tblPatientProcedures, SingleForm view, with a combobox control bound to field ProcedureID, RowSource: tblProcedures, to pick a procedure when adding a new record and display the procedure name of existing records. then a 2nd level subform, bound to tblPatProcLocations, with a combobox control bound to LocationID, RowSource: tblLocations, again to pick or display a location. if i were using A2000 - A2003, i'd set the DefaultView of the level2 subform to Datasheet. then i'd add a level3 subform, bound to tblLocSamples, DefaultView also as Datasheet, with a combobox control bound to SampleID, RowSource: tblSamples, and another combobox bound to ResultID, RowSource: tblResults. (if you're using A2007, i don't know what options may be available; if you're using A97, a different method is needed - but let's not go there unless we have to.) so you open the form, add a patient record or find an existing one in the combobox in the form Header. click on the 2nd tab, and add or go to a procedure record (you can use an combobox here, too - or a listbox - to find a specific procedure record for the selected patient). then enter or go to a location record in the datasheet, and enter or edit sample records in the subdatasheet of each location record. hth "CuriousMark" wrote in message ... I need help with design of a database I am writing to keep track of procedures that I do on patients. For each patient, one or more procedures can be done. For each procedure, one or more locations can be sampled (I want to keep track of the sequence of locations sampled, i.e. 1, loc2R; 2, loc2L; 3, loc7; etc). For each location, one or more samples (pass1, pass2, pass3, etc.) can be taken. For each sample there is a result (pos, neg, non-diagnostic). I keep getting lost trying to create a form or sequence of forms that will enable me to enter all the data. It seems that I need a subform (Location:Result) inside a subform (ProceduLocation) inside another subform (Name:Procedure). On the highest level form (Name) I want to be able to select from the existing names (shown as LastName & ", " & FirstName) or enter a new name. Any help with fixing my database design would be much appreciated. Thanks. |
#5
|
|||
|
|||
Serial one-to-many tables
Thanks Steve. Very similar to tina's suggestions, which is reassuring. As I
said in response to tina, I'll have to print these answers out and think about how to put this all together. I appreciate the help. "Steve" wrote: TblPatient PatientID FirstName LastName TblProcedure ProcedureID Procedure TblLocation LocationID Location TblPatientProcedure PatientProcedureID PatientID ProcedureID TblPatientProcedureLocation PatientProcedureLocationID PatientProcedureID LocationID TblSampleResultID SampleResultID SampleResult (Pos, Neg, Non-Diagnostic) TblPatientProcedureLocationSample PatientProcedureLocationSampleID PatientProcedureLocationID SampleName SampleResultID Steve "CuriousMark" wrote in message ... I need help with design of a database I am writing to keep track of procedures that I do on patients. For each patient, one or more procedures can be done. For each procedure, one or more locations can be sampled (I want to keep track of the sequence of locations sampled, i.e. 1, loc2R; 2, loc2L; 3, loc7; etc). For each location, one or more samples (pass1, pass2, pass3, etc.) can be taken. For each sample there is a result (pos, neg, non-diagnostic). I've come up with this series of tables: tblNames NameID LastName FirstName tblProcedure ProcedureID NameID ProcedureDate ProcedureType tblLocation LocationID ProcedureID LocationSequence LocationName tblResult ResultID LocationID PassNumber PassResult I keep getting lost trying to create a form or sequence of forms that will enable me to enter all the data. It seems that I need a subform (Location:Result) inside a subform (ProceduLocation) inside another subform (Name:Procedure). On the highest level form (Name) I want to be able to select from the existing names (shown as LastName & ", " & FirstName) or enter a new name. Any help with fixing my database design would be much appreciated. Thanks. |
#6
|
|||
|
|||
Serial one-to-many tables
HIPAA compliance.
oops! i can't believe i screwed that up (i've taken classes that include HIPAA information) and of course my acronym was *wrong*!! smacks self upside the head as for the table design and suggested form/subforms setup, yeah, it's a lot to take in. if the structure has you a bit stumped, a refresher on relational design principles will probably clear it up, see http://www.accessmvp.com/JConrad/acc...abaseDesign101 for links to resources. if it's the form/subform setup that's hard to visualize, let me know what version of Access you're working with; maybe i can whip up a demo db for you. hth "CuriousMark" wrote in message ... Wow. Thanks very much tina. I'll have to print this out and work through it a bit. I'm not exactly a novice at Access, but not experienced enough to understand your answer on the first read . Thanks also for the advice on HIPAA compliance. It is an issue and has been taken into account on several levels. "tina" wrote: well, you're on the right track. i'd add a few supporting tables, and rearrange a couple of the others a bit, as tblPatients PatientID (primary key) LastName FirstName okay so far, except note that i changed the table name and primary key fieldname, to be more descriptive. keep in mind that Access is not a secure repository for sensitive data such as social security numbers; if you need to keep that sort of data, you should look at using a server-based solution such as SQLServer. also, your use of the word patients indicates human medical data. if that's correct, and if you're in the USA, your data storage/handling solution needs to be HIIPA-compliant; again, data security is an issue here. tblProcedures ProcedureID (pk) ProcedureName this would be a list of all procedures that you can perform, and nothing else. do NOT include any fields that indicate a specific person or a specific performance of a procedure. tblLocations LocationID (pk) LocationName this would be a list of all locations where an instance of a procedure may be performed, and nothing else. tblSamples SampleID (pk) SampleDescription this would be a list of all possible sample descriptions - or names, if that makes more sense to you - such as pass1, pass2, pass3, etc. and nothing else. tblResults ResultID (pk) ResultDescription this would be a list of all possible result descriptions - or names - such as pos, neg, non-diagnostic, etc. and nothing else. tblPatientProcedures PatProcID (pk) PatientID (foreign key from tblPatients) ProcedureID (fk from tblProcedures) ProcedureDate the table relationships a tblPatients.PatientID 1:n tblPatientProcedures.PatientID tblProcedures.ProcedureID 1:n tblPatientProcedures.ProcedureID make sure you set the relationships in the Relationships window, and enforce referential integrity. tblPatProcLocations ProcLocID (pk) PatProcID (fk from tblPatientProcedures) LocationID (fk from tblLocations) Sequence the relationship is: tblPatientProcedures.PatProcID 1:n tblPatProcLocations.PatProcID tblLocations.LocationID 1:n tblPatProcLocations.LocationID ditto above re setting relationships. tblLocSamples LocSamID (pk) ProcLocID (fk from tblPatProcLocations) SampleID (fk from tblSamples) ResultID (fk from tblResults) the relationships would be: tblPatProcLocations.ProcLocID 1:n tblLocSamples.ProcLocID tblSamples.SampleID 1:n tblLocSamples.SampleID tblResults.ResultID 1:n tblLocSamples.ResultID exactly how you set up your data entry form is determined partly by the work flow, partly by the media that you'll be entering data from (i'm assuming a sheet of paper with data organized in a particular order), and partly on your skill level. the work flow you indicated below; and your skill level can be increased to meet your development needs - trust me, it can the source media we can't see, so you'll have to use your best judgment on making the form suit your needs there. i might start with a mainform, bound to tblPatients. probably an unbound combobox control in the form's Header section, RowSource: tblPatients, to select a particular patient record. perhaps a tab control, with the patient fields on the first page. then a subform on the second page of the tab control, bound to tblPatientProcedures, SingleForm view, with a combobox control bound to field ProcedureID, RowSource: tblProcedures, to pick a procedure when adding a new record and display the procedure name of existing records. then a 2nd level subform, bound to tblPatProcLocations, with a combobox control bound to LocationID, RowSource: tblLocations, again to pick or display a location. if i were using A2000 - A2003, i'd set the DefaultView of the level2 subform to Datasheet. then i'd add a level3 subform, bound to tblLocSamples, DefaultView also as Datasheet, with a combobox control bound to SampleID, RowSource: tblSamples, and another combobox bound to ResultID, RowSource: tblResults. (if you're using A2007, i don't know what options may be available; if you're using A97, a different method is needed - but let's not go there unless we have to.) so you open the form, add a patient record or find an existing one in the combobox in the form Header. click on the 2nd tab, and add or go to a procedure record (you can use an combobox here, too - or a listbox - to find a specific procedure record for the selected patient). then enter or go to a location record in the datasheet, and enter or edit sample records in the subdatasheet of each location record. hth "CuriousMark" wrote in message ... I need help with design of a database I am writing to keep track of procedures that I do on patients. For each patient, one or more procedures can be done. For each procedure, one or more locations can be sampled (I want to keep track of the sequence of locations sampled, i.e. 1, loc2R; 2, loc2L; 3, loc7; etc). For each location, one or more samples (pass1, pass2, pass3, etc.) can be taken. For each sample there is a result (pos, neg, non-diagnostic). I keep getting lost trying to create a form or sequence of forms that will enable me to enter all the data. It seems that I need a subform (Location:Result) inside a subform (ProceduLocation) inside another subform (Name:Procedure). On the highest level form (Name) I want to be able to select from the existing names (shown as LastName & ", " & FirstName) or enter a new name. Any help with fixing my database design would be much appreciated. Thanks. |
#7
|
|||
|
|||
Serial one-to-many tables
On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "SubForm3Levels.mdb" which illustrates how to do this. You can find it he http://www.rogersaccesslibrary.com/f...ts.asp?TID=263 Interestingly, I used a medical paradigm for this sample too. Visits --- Procedures --- Complications --- Interventions -- --Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L "CuriousMark" wrote in message ... I need help with design of a database I am writing to keep track of procedures that I do on patients. For each patient, one or more procedures can be done. For each procedure, one or more locations can be sampled (I want to keep track of the sequence of locations sampled, i.e. 1, loc2R; 2, loc2L; 3, loc7; etc). For each location, one or more samples (pass1, pass2, pass3, etc.) can be taken. For each sample there is a result (pos, neg, non-diagnostic). I've come up with this series of tables: tblNames NameID LastName FirstName tblProcedure ProcedureID NameID ProcedureDate ProcedureType tblLocation LocationID ProcedureID LocationSequence LocationName tblResult ResultID LocationID PassNumber PassResult I keep getting lost trying to create a form or sequence of forms that will enable me to enter all the data. It seems that I need a subform (Location:Result) inside a subform (ProceduLocation) inside another subform (Name:Procedure). On the highest level form (Name) I want to be able to select from the existing names (shown as LastName & ", " & FirstName) or enter a new name. Any help with fixing my database design would be much appreciated. Thanks. |
#8
|
|||
|
|||
Serial one-to-many tables
Thanks Roger. I will check it out.
"Roger Carlson" wrote: On my website (www.rogersaccesslibrary.com), is a small Access database sample called "SubForm3Levels.mdb" which illustrates how to do this. You can find it he http://www.rogersaccesslibrary.com/f...ts.asp?TID=263 Interestingly, I used a medical paradigm for this sample too. Visits --- Procedures --- Complications --- Interventions -- --Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L "CuriousMark" wrote in message ... I need help with design of a database I am writing to keep track of procedures that I do on patients. For each patient, one or more procedures can be done. For each procedure, one or more locations can be sampled (I want to keep track of the sequence of locations sampled, i.e. 1, loc2R; 2, loc2L; 3, loc7; etc). For each location, one or more samples (pass1, pass2, pass3, etc.) can be taken. For each sample there is a result (pos, neg, non-diagnostic). I've come up with this series of tables: tblNames NameID LastName FirstName tblProcedure ProcedureID NameID ProcedureDate ProcedureType tblLocation LocationID ProcedureID LocationSequence LocationName tblResult ResultID LocationID PassNumber PassResult I keep getting lost trying to create a form or sequence of forms that will enable me to enter all the data. It seems that I need a subform (Location:Result) inside a subform (ProceduLocation) inside another subform (Name:Procedure). On the highest level form (Name) I want to be able to select from the existing names (shown as LastName & ", " & FirstName) or enter a new name. Any help with fixing my database design would be much appreciated. Thanks. |
Thread Tools | |
Display Modes | |
|
|