Mapping values to labels

[Pharmnate]

First off - profuse apology. I am a very new access user. I have tried searching extensively for the answer to this but I am probably not using the correct terminology to get the answer I want. I'm a pharmacist and rather unfamiliar with coding/complex use of these programs. I am a quick learner and if you think my question below is well described somewhere please link me and I will go after it.



Problem: I have a huge report for a years worth of drug prescriptions. Over 90,000 rows. I have a diagnosis code for each order, these are varied ~ a few hundred. So I am trying to describe for what diseases these drugs are being used for. The problem with the diagnosis code is they are very granular and any pivot chart/table is lost in the minuita. I don't need that deep description.

For example:

J01.80	Other acute sinusitis, recurrence not specified
J01.01	Acute recurrent maxillary sinusitis
N39.0	Urinary tract infection, site unspecified
R35.0	Urinary frequency
N30.00	Acute cystitis without hematuria
J01.00	Acute maxillary sinusitis, recurrence not specified
J02.0	Strep pharyngitis
R50.9	Fever, unspecified fever cause
L03.116	Cellulitis of left lower extremity
J20.8	Acute bronchitis due to other specified organisms
J01.00	Acute maxillary sinusitis, recurrence not specified
J01.90	Acute sinusitis, recurrence not specified, unspecified location
J02.9	Acute pharyngitis, unspecified pharyngitis type
J01.00	Acute maxillary sinusitis, recurrence not specified
S20.111A	Breast abrasion, right, initial encounter
H65.02	Acute serous otitis media of left ear, recurrence not specified
H66.002	Acute suppurative otitis media of left ear without spontaneous rupture of tympanic membrane, recurrence not specified

I would like to map the J01.00, J01.80 and J01.90 code (and similar codes,there are many) to the general label of just "sinusitis" in another column. Same could be said of H66.002 and H65.02 to "Otitis Media".

What would be the best (if even possible) way to set up these mapping definitions so I can continue to add data to this from month to month and have these general labels assigned as diagnosis codes come across. I get the report in excel and transfer it to Access. I am using Access 2010.

Thanks in advance for your assistance.

-N

[June7]

As you said, need another column - in DiagnosisCodes table and do data entry to populate the field. So how many diagnosis codes?

Join DiagnosisCodes table to Orders table by linking on the common Code fields to retrieve related info into report.

Not sure why you even mention pivot chart/table.

[Pharmnate]

I'm afraid I am not following you well. I can definitely create another table with all the codes and the assigned labels I want. You mentioned "join" and "link", I'm not sure what that means or how to do that.

I am using a pivot chart/table to analyze my data. I was just framing the problem by saying they aren't useful now since there are hundreds of diagnoses and just a cloud of data. Using the mapping will clean that up and allow me to use those better.

[June7]

Primary principle behind relational database is 'to not duplicate data'. Data is distributed to multiple tables by rules of 'normalization'. Then queries are built to bring the related data together for outputting, generally as a report. Tables are related by primary and foreign key fields which are linked in query design via JOIN clause in SQL statement. Basic Access functionality. Have you completed an introductory tutorial book?

[orange]

I suggest you post some sample data from your 90,000 records, and
a list of the diagnosis with some description/rationale for what categorization you need.

What do the J, N, R, and H etc represent?? I looked at this and some others with google, but found nothing.

[ssanfu]

@orange.....

From what I know, the codes are ICD-10 (International Classification of Diseases, 10^th Revision).

See

Acute pharyngitis, unspecified - J02.9
https://icd10coded.com/cm/ch10/J00-J06/J02/J02.9/

ICD-10-CM Official Guidelines for Coding and Reporting
https://icd10coded.com/doc/2017-ICD-...Guidelines.pdf

[orange]

Thanks Steve.
Do you have any idea (best guess) for this that the OP posted

So I am trying to describe for what diseases these drugs are being used for.

Seems he wants some roll-up, but hasn't provided info.

[ssanfu]

To me, it sounds like the OP wants to be able to "group" ICD-10 codes to "his own categories"... not necessarily the way the ICD-10 groups them.

For instance, chapter 10 is (pg 47 of the above ICD -10 pdf link)

Chapter 10: Diseases of the Respiratory System (J00-J99)

there are these codes all dealing with sinusitis:
J01.00 Acute maxillary sinusitis, recurrence not specified
J01.01 Acute recurrent maxillary sinusitis
J01.80 Other acute sinusitis, recurrence not specified
J01.90 Acute sinusitis, recurrence not specified, unspecified location
(and maybe more)

and
where this code deals with pharyngitis:
J02.9 Acute pharyngitis, unspecified pharyngitis type



I would do like June suggests (in Post #2) and have another table to have the main categories (Sinusitis would be one) to link to the existing table of records to be able to "group" the ICD-10 codes.


But I'm guessing....

[orange]

Yes I agree--seems OP wants to take a standardized categorization/codification and interject his own??? Not a good scene.
But if he has a classification at a higher level, then define it and use it so you can get to that higher level without losing the ICD info.

[BogyOne]

One pharmacist to another - what about using ATC codes as labels?

Another question is what you want to have in your report and break down these categories in sensible level of detail unique values (common denominatives) to be captured in individual fields (=columns), e.g. disease, recurrence.

But something tells me you might be best off by grouping some categories in separate table and then query joined tables for groups leaving out detailed categories.