Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Link Tables for Comparison

Status
Not open for further replies.
shelby55

shelby55

Technical User
Jun 27, 2003
1,229
CA
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.
 
Sort by date Sort by votes
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
 
Upvote 0 Downvote
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.


 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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.

 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

JayRo71
  • Locked
  • Question
Importing only unique records from excel tables 1
Replies
10
Views
939
SkipVought
S
raywood1
  • Locked
  • Question
Reducing a Massive Linked Table
Replies
4
Views
769
dhookom
dhookom
MrStohler
  • Locked
  • Question
Beginner Q: How to separate data from Excel into related tables and maintain relationship? 1
Replies
5
Views
706
SkipVought
S
puforee
  • Locked
  • Question
Best way to setup tables for multible related data sets
Replies
1
Views
531
dhookom
dhookom
Lidias13
  • Locked
  • Question
Odata contains fields from multiple tables in single table with different field names - for same
Replies
1
Views
537
dhookom
dhookom

Part and Inventory Search

Sponsor

Back
Top