If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
And v. Or and Not Like for in two fields
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, |
#2
|
|||
|
|||
And v. Or and Not Like for in two fields
On Wed, 19 May 2010 07:08:01 -0700, Chuck W
wrote: 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 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, |
#3
|
|||
|
|||
And v. Or and Not Like for in two fields
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. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Chuck W" wrote: 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, |
#4
|
|||
|
|||
And v. Or and Not Like for in two fields
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). -- Daryl S "Chuck W" wrote: 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, |
#5
|
|||
|
|||
And v. Or and Not Like for in two fields
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 "Daryl S" wrote: 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). -- Daryl S "Chuck W" wrote: 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, |
#6
|
|||
|
|||
And v. Or and Not Like for in two fields
On Wed, 19 May 2010 09:55:01 -0700, Chuck W
wrote: 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 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 "Daryl S" wrote: 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). -- Daryl S "Chuck W" wrote: 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, |
Thread Tools | |
Display Modes | |
|
|