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?

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?

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?

?!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?

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?

Bitte benutzen Sie nicht mehr meinen Acvount. Ich gehöre nicht zur
Microsoft-Discussionsgruppe. Danke


Am 02.04.2008 21:28 Uhr schrieb "Access User" unter
in
:

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?

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?