Access compare wildcard values to table

Question

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*"));

Answer 1

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])