×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Link Tables for Comparison

thread700-1653500

Link Tables for Comparison

Link Tables for Comparison

(OP)
Hi

I am using Access 2003.  

I'm trying to figure out the structure of my database before proceeding and therefore looking for guidance.

Firstly, as background, coding is when a patient's course in hospital is coded using a specific nomenclature.  So every diagnosis has a code like pneumonia is J18, to show the patient was admitted via the emergency department the entry code = E etc.  There are once occurring fields like chart number, account number, CMG and then multi-occurring fields like procedures and diagnoses. Each chart will have different numbers of diagnoses up to 25 i.e. some may only have diagnoses in occurrence 1 to 3, others will have 1 to 7, up to 25.  

So I want to create a database so that the original coders' data can be entered (or is available if I can get a data dump from the hospital) and then my coding results.  I will want it so that diagnosis occurrence #1 for original codes matches with my occurrence #1 code so that I can run reports to see if they differ.  I'll want to create the database so that the data can be entered via a form.  

How will I set up the database so that I can link all of this?  Will I have a main table for the once occurring codes but x 2 so that I have original versus new?  

Any help greatly appreciated.    

RE: Link Tables for Comparison

If I were you, I would read up on table structures and data normalization. You should not have repeat fields suggested by "multi-occurring fields like procedures and diagnoses".

Duane
Hook'D on Access
MS Access MVP

RE: Link Tables for Comparison

(OP)
Hi Duane

I'm sorry but you're not understanding the database structure: these codes are used to describe the patient's course in hospital so it may take 1 to 25 diagnosis codes to do so.  The combination of diagnosis code and diagnosis type means that no one code is repeated but you would still have 1 to 25 for each patient.  

This would be the "multi" table in a one to many relationship.
 

 

RE: Link Tables for Comparison

Why don't you take a stab at what you think your table structure should be so we can provide direction. IMO, 1 to 25 diagnosis codes should create 1 to 25 related records. There is no way that I would create multiple diagnosis fields in a single table.

Duane
Hook'D on Access
MS Access MVP

RE: Link Tables for Comparison

(OP)
Hi

I'm open to ideas but I guess I'm not explaining it well thus my query for assistance.

The data per patient visit in software to collect this has many tables:
main table which includes the singularly occurring fields like name, admit date, separation date, most responsible diagnosis (occurrence 1 for dx), principle procedure (occur 1) etc.

diagnosis table where all diagnosis are in long format:
chartno   diagnosis occur   diagnosis  diagnosis type
12345          1               J18          M
12345          2               I25          2 ....etc.

procedure table is the same as diagnosis in that it is in long format.

Having said that, the data for an entire patient visit is submitted to the data holding companing as one line of data being one abstract so all data is in "wide"format:
chartno  acctno  entry  dx1 dxtyp1 dx2 dxtyp2.....etc.

So wide or long format doesn't matter to me so as long as I can compare the "original codes" to the "reabstracted codes".

Does that describe it any better?  Thanks.
 

RE: Link Tables for Comparison

I agree with Duane, show us your table structures. Multioccurring fields implies  lack of normalization.

Here's a link to a patient care data model that may give some ideas about related tables. This model is NOT intended to represent your situation. It is an existing data model that some one has provided concerning Patient Care.

http://www.databaseanswers.org/data_models/patient_care/index.htm

RE: Link Tables for Comparison

(OP)
Hi

Thanks for the reference start off with the model you provided.  This model is where the patient is only noted once as the main ID with the various visits in another table.  

What I'm proposing (right or wrong) and a model I've used forever is that the main table has a unique ID of chart number (patient identifier) and account number (visit identifier) in it.  

From the unique ID, I link to the diagnosis table which has as many diagnoses as was coded for the patient.  As stated above, it's in long format:
chartno  acctno       diagnosis occur   diagnosis  diagnosis type
12345    33333             1               J18          M
12345    33333             2               I25          2
45678    44444             1               E105         M
45678    44444             2               K530         1 etc.


But I can do this differently by having only one row of data for diagnoses and showing as:

chartno  acctno  adm_date sep_date dx1 dxtyp1 dx2 dxtyp2 dx3 dxtype


Whatever way it's set up is fine but I need to be able to compare the original diagnoses to the reabstracted ones. Thanks.
 

RE: Link Tables for Comparison

I am a stickler for normalization. It isn't clear what structure you are comparing this to. You can typically create a crosstab query to "un-normalize" your data.

Duane
Hook'D on Access
MS Access MVP

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close