Access 2000 Set-Up Question

[Pat]

I am the skin care nurse at a long-term care center. I have to track certain
data on every patient in our facility so I created a table with the following
data fields:

Room #
Medical Record Number (Primary Key)
Name
Risk Score
Risk Score Date
Assessment Completed
Assessment Completed Date
Care Plan Potential Risk
Care Plan Potential Risk Date
Care Plan Actual
Care Plan Actual Risk Date

Okay I have no problem with this. My issue comes up in the next two tables
I want to create. I want to create a table where I can enter data for any
patient that actually has a wound, the fields would include:

Date Diagnosed
Type of Wound
If it was Admitted or Acquired in house (Y/N)
Location of Wound (on the body)

In the last table I want to be able to track certain criteria that need to
be updated every week on each wound:

Week #
Stage
Measurements
Family Updated (Y/N)
MD Updated (Y/N)
Care Plan Updated (Y/N)
Dietary Documentation (Y/N)


The problem is the Primary Key. Ideally I'd like to use the patient's
medical record number which works fine in the first table because there would
never be a case where a patient's basic data is entered twice. However, in
the other two tables there would be, more often than not, multiple entries
for a patient.

What is the best way to primary key such a system? Any help or advice you
can offer would be greatly appreciated!

 

[NevilleT]

Hi Pat.

Not too much of a problem. In database design, when you have two tables
take the following approach. If the tables are A and B, ask the following
questions.
Can one of A have more than one of B?
Can one of B have more than one of A?
If the answer is Yes and Yes, you need a table in the middle to combine
them. Example. Can one patient have more than one wound? Yes. Can one
wound have more than one patient? Yes. So, you need a Wound/Patient table.
One table lists type of wounds and then patient x and wound y are combined in
a third table called something line tblPatientWound.
I would have one table for patients, one for wounds, one for wound/patients
with for your groups two and three. (Date Diagnosed etc, a and Week # etc.
Would also set up reference tables for Locations, Weeks, and Stage. If you
want to do further reporting by week, or location or stage you can easily run
reports based on these tables. For example if you want to look at all
injuries by arm, you can do so.

I hope this helps.

Neville Turbit
www.projectperfect.com.au

 

[Arvin Meyer MVP]

The Medical Record Number is the Foreign Key, on the many side table. Use an
Autonumber as the Primary Key.

BTW, if the patient can have more than 1 admittance, most of the information
in your main table also needs to be in a sub table, and the link should be
between your 2 news tables and the admittance table.

 

[leroy]

I am the skin care nurse at a long-term care center. I have to track
certain
data on every patient in our facility so I created a table with the
following
data fields:

Room #
Medical Record Number (Primary Key)
Name
Risk Score
Risk Score Date
Assessment Completed
Assessment Completed Date
Care Plan Potential Risk
Care Plan Potential Risk Date
Care Plan Actual
Care Plan Actual Risk Date

Okay I have no problem with this. My issue comes up in the next two
tables
I want to create. I want to create a table where I can enter data for any
patient that actually has a wound, the fields would include:

Date Diagnosed
Type of Wound
If it was Admitted or Acquired in house (Y/N)
Location of Wound (on the body)

In the last table I want to be able to track certain criteria that need to
be updated every week on each wound:

Week #
Stage
Measurements
Family Updated (Y/N)
MD Updated (Y/N)
Care Plan Updated (Y/N)
Dietary Documentation (Y/N)


The problem is the Primary Key. Ideally I'd like to use the patient's
medical record number which works fine in the first table because there
would
never be a case where a patient's basic data is entered twice. However,
in
the other two tables there would be, more often than not, multiple entries
for a patient.

What is the best way to primary key such a system? Any help or advice you
can offer would be greatly appreciated!

 

[Larry Daugherty]

Each of the new tables will have its own Primary Key. I recommend the
Autonumber datatype. They will each be related to the main Patient
table and will have the Primary Key of the Main table entered as a
Foreign Key in the related table (children claim their parents,
parents don't list their children). The Foreign Key field must be of
the same datatype as the Primary Key of the Main table. It is a
common practice to name the fields exactly the same. Note: the term
"Foreign Key" is a way to refer to that special field in the
related/child record. The documentation will not use that term but
you'll hear it used frequently in these newsgroups.

Once your tables are designed, open the Relationships window and, for
each of the new tables, draw a line from the Primary Key of the Main
table to the Foreign Key field in the related table. There will now
be a line showing the link. Doubleclick that line and establish the
join as one to many with the Main table being the One side and the
related table being the Many side. Set Referential Integrity "On".
Check "Cascade Deletes".

Post back as questions arise.

HTH