And v. Or and Not Like for in two fields

[Chuck W]

Hello,
I have a field in a patient table called PrimaryDiagnosisCode which has a
five character value that is either numeric or alpha numeric. I also have a
field called AllSecondaryDiagCodes which has these same five character values
but can have several of them all separated by semi-colons. For instance, a
value can look like the following:

;25002;99760;4439;2809;V5867;V6284;412;496;41400;2

I have been given a list of codes to find in either field (Primary or
secondary) along with exception codes for the secondary. For instance, if
the code 64880 appears in the secondary code, then I need to excldue it from
my query results. I am trying to figure out using the Design view grid what
to put in the criteria statement. For instance, If I am looking for codes
*;25000;* or *;25001;* or *;25002;* in my primary or secondary field but want
to exclude *;64880;* or 79029;* or *;77510;* in the secondary only how would
I do this?

Thanks,

 

[Tom van Stiphout]

On Wed, 19 May 2010 07:08:01 -0700, Chuck W

This smells of very bad database design. One reason you're having
these problems is because the codes are not spun off in their own
table. Fix that, and the query will become much easier.

If the list of codes you have been given is more or less permanent, it
will also be beneficial to store them in another table, so you can
query against that table rather than against specific values.

Let's change the world, one bad table at a time

-Tom.
Microsoft Access MVP

 

[Jerry Whittle]

You do it by changing how your data is stored in tables. You should have a
seperate table for DiagnosisCodes that looks something like so:

DCID VisitID DiagnosisCode DiagnosisCodeType
1 123 25000 P
2 123 25001 S
3 123 25002 S
4 123 V5867 S

DCID is just an autonumber to give you a primary key field.
VisitID is the foreign key that matches the primary key field in the Patient
or Visit table.
DiagnosisCode is your codes.
DiagnosisCodeType has P for primary and S for secondary.

With a table set up like so, you could create queries to do what you need
much easier.

 

[Daryl S]

Chuck -

In the query grid you will have both the Primary and Secondary fields
listed.
In the first criteria line for the Primary field, use this (using whatever
you are searching for):
In ("25000","25001","25002")
Put this in the second criteria line under the Secondary field:
Like "*;25000;*" or like "*;25001;*" or like "*;25002;*"

Add the Secondary field again and in both the first and second criteria
line, put this:

Not like "*;64880;*" AND Not like "*;79029;* AND not like "*;77510;*"

If you have a problem, post your SQL (go to SQL View and copy/paste into
your post).

 

[Chuck W]

Thanks Daryl,
Thanks solved the problem. I don't have control of the database by the way.
It is an extract of our hospital data sent to us by a vendor. I should have
mentioned this in my first note to address the apparent bad database design.

Chuck

 

[Tom van Stiphout]

On Wed, 19 May 2010 09:55:01 -0700, Chuck W

The classic solution would be to import that data in a relational db,
not necessarily in the exact same table layout as supplied.

Glad you were able to get past the immediate problem.

-Tom.
Microsoft Access MVP