0

I have a table that contains a field with ICD codes (death classification codes). I want to create another field that decodes the cause of death based on the ICD codes. Here are some of the codes I'm working with as reference:
Cancer: Anything that starts with the letter "C", and all codes "D00-D49"
Heart Disease: "I20-I52"
Diabetes: "E10-E14"
Accidents: Anything that starts with the letters "V" and "W", all codes "X00-X59"

Codes can be up to 4 characters long, so the codes for Heart Disease, for example, encompass I201, I313, etc.

Anyway, I created a query to add a field that decodes the cause of death that looks like this (CAUSE is the field with the ICD Code):

SELECT CAUSE, 
IIf(Left([CAUSE],1) In ('C'),"Cancer",
IIf(Left([CAUSE],2) In ('D0','D1','D2','D3','D4'),"Cancer",
IIf(Left([CAUSE],3) In ('G30'),"Alzheimer's disease",
IIf(Left([CAUSE],2) In ('I2','I3','I4'),"Heart Disease",
IIf(Left([CAUSE],3) In ('I51','I52'),"Heart Disease",
IIf(Left([CAUSE],2) In ('I6'),"Cerebrovascular disease",
IIf(Left([CAUSE],3) In ('E10','E11','E12','E13','E14'),"Diabetes",
IIf(Left([CAUSE],3) In ('J09','J10','J11','J12'),"Influenza/Pneumonia",
IIf(Left([CAUSE],3) In ('J40','J41'), "Chronic Lower Respiratory Diseases",
IIf(Left([CAUSE],2) In ('X6','X7'),"Suicide",
IIf(Left([CAUSE],3) In ('X80','X81','X82','X83','X84'),"Suicide",
IIf(Left([CAUSE],1) In ('V','W'),"Accident",
IIf(Left([CAUSE],2) In ('X0','X1','X2','X3','X4','X5'),"Accident",
"Other"
))))))))))))) 
AS SpecCause
FROM ALLDeaths;

This particular code works, but if I add another line, I get a "query expression too complex for expression" error. The same happened with the Switch function.

SELECT cause,
SWITCH(
cause LIKE 'C*', "Cancer",
cause = 'G30', "Alzheimer's Disease",
cause LIKE 'I2*', "Heart Disease",
cause LIKE 'I6*', "Cerebrovascular disease",
cause LIKE 'E10*', "Diabetes",
cause LIKE 'E11*', "Diabetes",
cause LIKE 'E12*', "Diabetes",
cause LIKE 'E13*', "Diabetes",
cause LIKE 'E14*', "Diabetes",
cause LIKE 'W*', "Accident"
)
AS SpecCause
FROM ALLDeaths;

The above code works, but if I add another line, I'll get the same "query too complex for expression" error. Is there an alternative I can use that allows me to decode all of the ICD values without a limit?

user3642531
  • 309
  • 4
  • 12

2 Answers2

4

I'd recommend skipping the code completely and just using a 2 column look up table. It could look something like:

Code CauseOfDeath
E10  Diabetes
E11  Diabetes
G30  Alzheimers Disease
...

(Since code can vary in length, you could join to it using Like instead of =.)

Edit: In your statements, you're trying to save some space by using 'C' for all Cancer. The ideal solution here would be to enter every ICD code into this table.

PowerUser
  • 11,583
  • 20
  • 64
  • 98
  • Actually, they need a table with LEGAL cause codes anyway, so no "range", but the table would have all legal codes. Such a table not only requires ZERO code, but the combo box on the form to select the cause can limit and restrict the codes to ONLY legal codes - very useful in data entry. There is ZERO reason for ANY code having to be written here, and the idea that a software developer has to be brought in to allow additional codes is oh so very wrong when using relational features of databases can solve this issue without ANY code! – Albert D. Kallal Aug 20 '14 at 21:03
  • Thanks for the suggestion about using a lookup table. While ideally I would enter EVERY code, there are literally thousands of codes, so manually entering everything isn't really possible. It would've been much easier if all this stuff was recorded at data entry but unfortunately the database wasn't designed that way, so I have a lot of records that need to be decoded retroactively. I'm actually going to go ahead and try the original suggestion when I get in the office tomorrow. I honestly never even though to join tables, thanks. – user3642531 Aug 21 '14 at 01:44
  • This actually worked for me, I used LIKE and concatenated the Code to a wildcard in the join. Thanks. – user3642531 Aug 21 '14 at 20:32
  • I'm glad one of my suggestions worked for you even if it wasn't the ideal one. In regards to the whole data set, you might try googling it if the list isn't unique to your situation. I tried that just now and found another stackoverflow question with some good official looking links: http://stackoverflow.com/questions/3653811/icd-9-code-list-in-xml-csv-or-database-format – PowerUser Aug 22 '14 at 18:59
0

I'd use another table with an ID/sequence, description, loCode, and hiCode. Then you can search for all the rows with codes between loCode and hiCode, if any.

Beth
  • 9,531
  • 1
  • 24
  • 43