0

This is a simple question on its own, but my dilemma is I'm comparing 1,000+ medical diagnosis codes to the overall ICD9 table.

I have a list of truncated codes to compare to the entire database. For example, I have truncated code 010, but the overall ICD9 database table has 010.01, 010.02, ....010.96 and should return all the values within 010*.

The truncated code lists I'm working from is variable and contain between 800-1,300+ wildcard codes and Access has a 40 variable limit in queries.

I found this help topic but didn't understand how to convert it to my needs. Below is the SQL to my query I have setup currently, but have shortened it to only a few values to search. If there is a way to either put the wildcard codes into its own table and joint to the ICD9 table, that would be great...but my expertise isn't available to know how to use the other table to search the wildcard.

SELECT [ICD9-10 Code Match].[ICD9 Code]
FROM [ICD9-10 Code Match]
WHERE ((([ICD9-10 Code Match].[ICD9 Code]) Like "010*" Or ([ICD9-10 Code Match].[ICD9 Code]) Like "011*" Or ([ICD9-10 Code Match].[ICD9 Code]) Like "012*" Or ([ICD9-10 Code Match].[ICD9 Code]) Like "013*" Or ([ICD9-10 Code Match].[ICD9 Code]) Like "014*" Or ([ICD9-10 Code Match].[ICD9 Code]) Like "015*" Or ([ICD9-10 Code Match].[ICD9 Code]) Like "016*" Or ([ICD9-10 Code Match].[ICD9 Code]) Like "017*" Or ([ICD9-10 Code Match].[ICD9 Code]) Like "018*" Or ([ICD9-10 Code Match].[ICD9 Code]) Like "042*" Or ([ICD9-10 Code Match].[ICD9 Code]) Like "0100*"));
Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
jeffrey406
  • 11
  • 1

1 Answers1

0

This should do:

SELECT [ICD9-10 Code Match].[ICD9 Code]
FROM [ICD9-10 Code Match]
WHERE [ICD9-10 Code Match].[ICD9 Code] IN
    (Select Left([Code], InStr([Code], ".") - 1) From [ICD9])
Gustav
  • 53,498
  • 7
  • 29
  • 55
  • Thanks Gustav! My goal is for anyone who may come in after me be able to understand what I've done. Although I like your method much better and it did work! I paraphrased it some to the following after I did a Find->Replace for the decimal point -> blank: SELECT [Code List].[Diagnosis Code], [ICD9-10 Code Match].Match FROM [ICD9-10 Code Match] INNER JOIN [Code List] ON [ICD9-10 Code Match].Match Like [Code List].[Diagnosis Code]; It seemed to work appropriately and easy for someone not familiar with the InStr formula. – jeffrey406 Jan 09 '17 at 17:34
  • I hate it when I have a brain buster, and the solution is so simple! Thanks for getting me over the roadblock. – jeffrey406 Jan 09 '17 at 17:37