Parent Child Tables and Relationships

[Access User]

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?

 

[Evan Keel]

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]

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

 

[Jeff Boyce]

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

As in JAS_1 and JAS_2 you mean?

?!Reviewer's Initials?!

Let's hope you never have John Adam Smith and Joe Arnold Smithson
reviewing?!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Bärbel Hofmann-Panke]

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

 

[Jeff Boyce]

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