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
|
|||
|
|||
Parent Child Tables and Relationships
I have a need to design a mdb file that revolves around two tables. My main
table is patient-specific and needs to have a patient ID field, a Date field and a reviewer field. I'm going to call them ID, MRADATE and REVINT. For any ID, there will be two records having the same MRADATE but different REVINT. There is also a child-subform/table which needs to be synched up with the main table. The child table needs to have an ID field, a REVINT field and an ANEURISM field. For any patient ID on a given MRADATE, each of the pair of REVINT will have a chance to read that patient's MRA exam and code their findings in the sub-table. There is an upper limit (4) on the number of ANEURISMs that can be entered per ID by a REVINT. At this point, it doesn't look as though I can have all of this in one relationship involving a parent and child table....I've worked around that by having a pair of parent and child tables, one per REVINT. My PK on each parent is ID and on each child is ID and ANEURISM. Although REVINT need to get entered, they are getting their respective values by 'default' within each pair of parent/child tables. Though this setup gave me the ability to have the one-to-many setup complete with referential integrity constraints and cascade edit/delete capability, it just seems that if I had the benefit of more time, it could've/would've been set up with one pair of tables. Am I right? |
#2
|
|||
|
|||
Parent Child Tables and Relationships
Your design is wrong. What if your rules change and you can have 3 REVINT
(btw, what is a REVINT?) enter findings? Or 4 or 10? What you want is a Patient Table with PatientID as primary key along with other patient information. Then you will want a child table with PatientID, MRADate, and MRANumber as primary keys. Also in this table will be REVINT and Finding. This will allow for a patient to have many MRAs, on many MRA dates, and many MRAs on a particlular date. You will have to enforce the cardinalities (ex. 4 anuerisms per patient) through code. Good Luck, Evan "Access User" wrote in message ... I have a need to design a mdb file that revolves around two tables. My main table is patient-specific and needs to have a patient ID field, a Date field and a reviewer field. I'm going to call them ID, MRADATE and REVINT. For any ID, there will be two records having the same MRADATE but different REVINT. There is also a child-subform/table which needs to be synched up with the main table. The child table needs to have an ID field, a REVINT field and an ANEURISM field. For any patient ID on a given MRADATE, each of the pair of REVINT will have a chance to read that patient's MRA exam and code their findings in the sub-table. There is an upper limit (4) on the number of ANEURISMs that can be entered per ID by a REVINT. At this point, it doesn't look as though I can have all of this in one relationship involving a parent and child table....I've worked around that by having a pair of parent and child tables, one per REVINT. My PK on each parent is ID and on each child is ID and ANEURISM. Although REVINT need to get entered, they are getting their respective values by 'default' within each pair of parent/child tables. Though this setup gave me the ability to have the one-to-many setup complete with referential integrity constraints and cascade edit/delete capability, it just seems that if I had the benefit of more time, it could've/would've been set up with one pair of tables. Am I right? |
#3
|
|||
|
|||
Parent Child Tables and Relationships
Well a few things....
The data are recorded on paper forms and need to get input into a computerized database. There will only ever be two REVINTs (sorry it meant reviewer's initials, and they are the same throughout). Each REVINT has read the same MRA film/scan from the patient (ID) which was film/scan was acquired on a particular MRADATE. There are certain findings that each REVINT can be asked to record as having been absent/present per patient film/scan and they are recorded in the parent table, BUT when it comes to aneurisms, then the number of them can vary from film to film as well as their properties, hence there's a sub-table per reviewer to record the properties of those aneurisms; not to put too fine a point on this, but the two reviewers examining the same IDs film can record different numbers of aneurims -- "Evan Keel" wrote: Your design is wrong. What if your rules change and you can have 3 REVINT (btw, what is a REVINT?) enter findings? Or 4 or 10? What you want is a Patient Table with PatientID as primary key along with other patient information. Then you will want a child table with PatientID, MRADate, and MRANumber as primary keys. Also in this table will be REVINT and Finding. This will allow for a patient to have many MRAs, on many MRA dates, and many MRAs on a particlular date. You will have to enforce the cardinalities (ex. 4 anuerisms per patient) through code. Good Luck, Evan "Access User" wrote in message ... I have a need to design a mdb file that revolves around two tables. My main table is patient-specific and needs to have a patient ID field, a Date field and a reviewer field. I'm going to call them ID, MRADATE and REVINT. For any ID, there will be two records having the same MRADATE but different REVINT. There is also a child-subform/table which needs to be synched up with the main table. The child table needs to have an ID field, a REVINT field and an ANEURISM field. For any patient ID on a given MRADATE, each of the pair of REVINT will have a chance to read that patient's MRA exam and code their findings in the sub-table. There is an upper limit (4) on the number of ANEURISMs that can be entered per ID by a REVINT. At this point, it doesn't look as though I can have all of this in one relationship involving a parent and child table....I've worked around that by having a pair of parent and child tables, one per REVINT. My PK on each parent is ID and on each child is ID and ANEURISM. Although REVINT need to get entered, they are getting their respective values by 'default' within each pair of parent/child tables. Though this setup gave me the ability to have the one-to-many setup complete with referential integrity constraints and cascade edit/delete capability, it just seems that if I had the benefit of more time, it could've/would've been set up with one pair of tables. Am I right? |
#4
|
|||
|
|||
Parent Child Tables and Relationships
?!Reviewer's Initials?!
Let's hope you never have John Adam Smith and Joe Arnold Smithson reviewing?! Regards Jeff Boyce Microsoft Office/Access MVP "Access User" wrote in message ... Well a few things.... The data are recorded on paper forms and need to get input into a computerized database. There will only ever be two REVINTs (sorry it meant reviewer's initials, and they are the same throughout). Each REVINT has read the same MRA film/scan from the patient (ID) which was film/scan was acquired on a particular MRADATE. There are certain findings that each REVINT can be asked to record as having been absent/present per patient film/scan and they are recorded in the parent table, BUT when it comes to aneurisms, then the number of them can vary from film to film as well as their properties, hence there's a sub-table per reviewer to record the properties of those aneurisms; not to put too fine a point on this, but the two reviewers examining the same IDs film can record different numbers of aneurims -- "Evan Keel" wrote: Your design is wrong. What if your rules change and you can have 3 REVINT (btw, what is a REVINT?) enter findings? Or 4 or 10? What you want is a Patient Table with PatientID as primary key along with other patient information. Then you will want a child table with PatientID, MRADate, and MRANumber as primary keys. Also in this table will be REVINT and Finding. This will allow for a patient to have many MRAs, on many MRA dates, and many MRAs on a particlular date. You will have to enforce the cardinalities (ex. 4 anuerisms per patient) through code. Good Luck, Evan "Access User" wrote in message ... I have a need to design a mdb file that revolves around two tables. My main table is patient-specific and needs to have a patient ID field, a Date field and a reviewer field. I'm going to call them ID, MRADATE and REVINT. For any ID, there will be two records having the same MRADATE but different REVINT. There is also a child-subform/table which needs to be synched up with the main table. The child table needs to have an ID field, a REVINT field and an ANEURISM field. For any patient ID on a given MRADATE, each of the pair of REVINT will have a chance to read that patient's MRA exam and code their findings in the sub-table. There is an upper limit (4) on the number of ANEURISMs that can be entered per ID by a REVINT. At this point, it doesn't look as though I can have all of this in one relationship involving a parent and child table....I've worked around that by having a pair of parent and child tables, one per REVINT. My PK on each parent is ID and on each child is ID and ANEURISM. Although REVINT need to get entered, they are getting their respective values by 'default' within each pair of parent/child tables. Though this setup gave me the ability to have the one-to-many setup complete with referential integrity constraints and cascade edit/delete capability, it just seems that if I had the benefit of more time, it could've/would've been set up with one pair of tables. Am I right? |
#5
|
|||
|
|||
Parent Child Tables and Relationships
As in JAS_1 and JAS_2 you mean?
"Jeff Boyce" wrote: ?!Reviewer's Initials?! Let's hope you never have John Adam Smith and Joe Arnold Smithson reviewing?! Regards Jeff Boyce Microsoft Office/Access MVP "Access User" wrote in message ... Well a few things.... The data are recorded on paper forms and need to get input into a computerized database. There will only ever be two REVINTs (sorry it meant reviewer's initials, and they are the same throughout). Each REVINT has read the same MRA film/scan from the patient (ID) which was film/scan was acquired on a particular MRADATE. There are certain findings that each REVINT can be asked to record as having been absent/present per patient film/scan and they are recorded in the parent table, BUT when it comes to aneurisms, then the number of them can vary from film to film as well as their properties, hence there's a sub-table per reviewer to record the properties of those aneurisms; not to put too fine a point on this, but the two reviewers examining the same IDs film can record different numbers of aneurims -- "Evan Keel" wrote: Your design is wrong. What if your rules change and you can have 3 REVINT (btw, what is a REVINT?) enter findings? Or 4 or 10? What you want is a Patient Table with PatientID as primary key along with other patient information. Then you will want a child table with PatientID, MRADate, and MRANumber as primary keys. Also in this table will be REVINT and Finding. This will allow for a patient to have many MRAs, on many MRA dates, and many MRAs on a particlular date. You will have to enforce the cardinalities (ex. 4 anuerisms per patient) through code. Good Luck, Evan "Access User" wrote in message ... I have a need to design a mdb file that revolves around two tables. My main table is patient-specific and needs to have a patient ID field, a Date field and a reviewer field. I'm going to call them ID, MRADATE and REVINT. For any ID, there will be two records having the same MRADATE but different REVINT. There is also a child-subform/table which needs to be synched up with the main table. The child table needs to have an ID field, a REVINT field and an ANEURISM field. For any patient ID on a given MRADATE, each of the pair of REVINT will have a chance to read that patient's MRA exam and code their findings in the sub-table. There is an upper limit (4) on the number of ANEURISMs that can be entered per ID by a REVINT. At this point, it doesn't look as though I can have all of this in one relationship involving a parent and child table....I've worked around that by having a pair of parent and child tables, one per REVINT. My PK on each parent is ID and on each child is ID and ANEURISM. Although REVINT need to get entered, they are getting their respective values by 'default' within each pair of parent/child tables. Though this setup gave me the ability to have the one-to-many setup complete with referential integrity constraints and cascade edit/delete capability, it just seems that if I had the benefit of more time, it could've/would've been set up with one pair of tables. Am I right? |
#7
|
|||
|
|||
Parent Child Tables and Relationships
While that would provide a way to differentiate two folks with the same
initials, which one is "_1" and which is "_2"?! A more user-friendly way to accomplish about the same thing would be to use an autonumber ID field that is NEVER displayed to the user, and use actual names (users generally understand peoples' names better than some code). Of course, then you get to worry about having two "John J. Doe"s as reviewers! Isn't working with real live people fun?! Regards Jeff Boyce Microsoft Office/Access MVP "Access User" wrote in message ... As in JAS_1 and JAS_2 you mean? "Jeff Boyce" wrote: ?!Reviewer's Initials?! Let's hope you never have John Adam Smith and Joe Arnold Smithson reviewing?! Regards Jeff Boyce Microsoft Office/Access MVP "Access User" wrote in message ... Well a few things.... The data are recorded on paper forms and need to get input into a computerized database. There will only ever be two REVINTs (sorry it meant reviewer's initials, and they are the same throughout). Each REVINT has read the same MRA film/scan from the patient (ID) which was film/scan was acquired on a particular MRADATE. There are certain findings that each REVINT can be asked to record as having been absent/present per patient film/scan and they are recorded in the parent table, BUT when it comes to aneurisms, then the number of them can vary from film to film as well as their properties, hence there's a sub-table per reviewer to record the properties of those aneurisms; not to put too fine a point on this, but the two reviewers examining the same IDs film can record different numbers of aneurims -- "Evan Keel" wrote: Your design is wrong. What if your rules change and you can have 3 REVINT (btw, what is a REVINT?) enter findings? Or 4 or 10? What you want is a Patient Table with PatientID as primary key along with other patient information. Then you will want a child table with PatientID, MRADate, and MRANumber as primary keys. Also in this table will be REVINT and Finding. This will allow for a patient to have many MRAs, on many MRA dates, and many MRAs on a particlular date. You will have to enforce the cardinalities (ex. 4 anuerisms per patient) through code. Good Luck, Evan "Access User" wrote in message ... I have a need to design a mdb file that revolves around two tables. My main table is patient-specific and needs to have a patient ID field, a Date field and a reviewer field. I'm going to call them ID, MRADATE and REVINT. For any ID, there will be two records having the same MRADATE but different REVINT. There is also a child-subform/table which needs to be synched up with the main table. The child table needs to have an ID field, a REVINT field and an ANEURISM field. For any patient ID on a given MRADATE, each of the pair of REVINT will have a chance to read that patient's MRA exam and code their findings in the sub-table. There is an upper limit (4) on the number of ANEURISMs that can be entered per ID by a REVINT. At this point, it doesn't look as though I can have all of this in one relationship involving a parent and child table....I've worked around that by having a pair of parent and child tables, one per REVINT. My PK on each parent is ID and on each child is ID and ANEURISM. Although REVINT need to get entered, they are getting their respective values by 'default' within each pair of parent/child tables. Though this setup gave me the ability to have the one-to-many setup complete with referential integrity constraints and cascade edit/delete capability, it just seems that if I had the benefit of more time, it could've/would've been set up with one pair of tables. Am I right? |
Thread Tools | |
Display Modes | |
|
|