Simplifying a query

[simon4amiee]

Hi guys,

I've created a query which does the trick, however when I add this criteria into 12 different field I comes back with argument too long etc. I wondered if there was a why to reduce this in any way.

To get round it Ive created 2 query and put them in a Union query which works but was hoping to skip that step and put into one query.


Between "C00*" And "C97*" Or Between "D00*" And "D09*" Or Like "D32*" Or Like "D33*" Or Like "D352" Or Like "D353" Or Like "D354" Or Between "D37*" And "D48*"

 

[plog]

1 method for sure, maybe another. First the another:

Do those prefixes (e.g.C00, D32, D353) mean something unto themselves? I'm thinking if so, you should store them in another field. Like so:

CurrentCodeField
C0098712
C0076541
D091222
D092111

CodePrefixField, CodeField
C00, 98712
C00, 76541
D09, 1222
D09, 2111

That would be the proper way to store your data if those prefixes mean something. If not, the only other method I see is this:

Build a function and put your criteria logic in there:

Public Function IsValid(in_Code) As Boolean
    ' takes a code value (in_Code) and determines if it is to be included in query

ret = False        ' return value, by default is False

If in_Code Like "D32*" Then ret = True

' put all other logic below


IsValid = ret   

End Function

Then to use it in a query, you make a new field and pass it the code:

ValidCode: IsValid([CodeField])

and underneath it, in the criteria section you put "True"

 

[simon4amiee]

They are indeed codes:

eg

C00.1, C00.2, C00.3, C00.4
D32.1, D32.2, D32.3, D32.4

These are diagnosis codes and are required in this output, however locally they are stored in their own table.

 

[simon4amiee]

This was the criteria I had to replicate, and to note there are 13 Diagnosis fields in total, all the same and would vary on how they are populated dependant obviously on a the number of diagnosis's recorded.

---------

Records should be selected for inclusion in the data feed where the following codes appear in ANY of the ‘Primary Diagnosis (ICD)’ or ‘Secondary diagnosis (ICD)’ fields:

C00 to C97, D00 to D09, D32, D33, D35.2 to D35.4, D37 to D48 (all inclusive)

Some valid codes have only 2 significant digits (after the C/D), others have 3. Where we have only provided 2 digits and there are multiple sub-classifications e.g. C00.0, C00.1 to C00.9 it is implied that all sub-classifications should be included in the extract i.e. all codes starting with C00 should be included. However, where we have specified to 3 digits e.g. D35.2 to D35.4 then we only require the codes with this explicit range (inclusively) e.g. not D35.1. For codes where there are only 2 digits e.g. C61 we expect the national standard of padding with ‘X’ to be followed.

 

[plog]

Data needs to be stored as discretely as possible. You don't store "John Quincy Adams" in 1 field, you store it in 3. Same with your data, especially since you want to work with it at a level down from what you currently have.

My advice is to break it down to the level you want to work with it. Then what you can do to implement your query is to create a definition table which would hold the valid codes and you then INNER JOIN to your data source:

ValidCodes
Code
C00
C01
C02
...
C97

This may seem like more work, but I've been there. I guarantee you run this data, then someone goes through an ICD book and wants the data reran to exclude C54 and C78 and include D12 or some other permutation of changes.

 

[CJ_London]

Between "C00*" And "C97*" Or Between "D00*" And "D09*" Or Like "D32*" Or Like "D33*" Or Like "D352" Or Like "D353" Or Like "D354" Or Between "D37*" And "D48*"

Don't think this will work as you want anyway

Between "C00*" And "C97*" will not return C971

What you can do is split your code in the query - see representation of the query builder below

 ICD           PCode:left(ICD,1)   MCode:mid(ICD,2,2)
                 C                           between"00" and "97"
                 D                           between "00" and "09" or "32" or "33" or between "37" and "48"
 In ("D352" ,"D353","D354")