Link Tables for Comparison

[shelby55]

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.

 

[dhookom]

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

 

[shelby55]

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.

 

[dhookom]

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

 

[shelby55]

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.

 

[jedraw]

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

 

[dhookom]

The patient diagnosis codes are actually found on data model for patient tests

Duane
Hook'D on Access
MS Access MVP

 

[shelby55]

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.

 

[dhookom]

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