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
|
|||
|
|||
Filtering between two columns
I have to columns that I want to try and filter out at the same time. Not
really sure if this is possible. example Column 1 Column 2 770/005 K01001 770/005 K01002 770/005 K01003 770/006 W01001 770/006 K01004 770/007 Z01001 770/007 W01002 What I want to do is pull all of the column 1's that have only K values, then pull column 1's that have mixed k,w values and finally column 1's with mixed w,z values. I have tried to think off an IIF statement but couldn't quite get my head around it. I can't see how a Like or Not Like statement will work either. |
#2
|
|||
|
|||
Filtering between two columns
On Sat, 17 Apr 2010 09:55:01 -0700, ddoblank
wrote: I have to columns that I want to try and filter out at the same time. Not really sure if this is possible. example Column 1 Column 2 770/005 K01001 770/005 K01002 770/005 K01003 770/006 W01001 770/006 K01004 770/007 Z01001 770/007 W01002 What I want to do is pull all of the column 1's that have only K values, then pull column 1's that have mixed k,w values and finally column 1's with mixed w,z values. I have tried to think off an IIF statement but couldn't quite get my head around it. I can't see how a Like or Not Like statement will work either. It sounds like you need three queries, not one: SELECT Column1 FROM tablename WHERE Column2 LIKE "K*" AND Column1 NOT IN (SELECT Column1 FROM tablename AS X WHERE X.Column1 = Table1.Column1 AND X.Column2 NOT LIKE "K*") SELECT Column1 FROM tablename WHERE Column2 LIKE "[KW]*" AND Column1 NOT IN (SELECT Column1 FROM tablename AS X WHERE X.Column1 = Table1.Column1 AND X.Column2 NOT LIKE "[KW]*") SELECT Column1 FROM tablename WHERE Column2 LIKE "[WZ]*" AND Column1 NOT IN (SELECT Column1 FROM tablename AS X WHERE X.Column1 = Table1.Column1 AND X.Column2 NOT LIKE "[WZ]*") -- John W. Vinson [MVP] |
#3
|
|||
|
|||
Filtering between two columns
I think you want just those column 1 values that have a column 2 value that
begins with K and has no column 2 values that begin with any other letter. SELECT Distinct [Column 1] FROM [TheTable] WHERE [Column 2] Like "K*" AND NOT EXISTS (SELECT * FROM [TheTable] as Temp WHERE [Column 1] NOT LIKE "K*") Next you want records have a column 2 with a K value and a W value. You did not say if you want that limited to only K and W and if they have a Z that is ok. SELECT [Column 1] FROM [TheTable] WHERE Exists (SELECT * FROM [TheTable] as Temp WHERE [Column 1] LIKE "K*") AND Exists (SELECT * FROM [TheTable] as Temp WHERE [Column 1] LIKE "W*") AND NOT Exists (SELECT * FROM [TheTable] as Temp WHERE [Column 1] Not LIKE "K*" AND [Column 1] Not Like "W*") Your third query should be similar and I leave it to you to figure it out. This WILL be SLOW if you have any large set of records. If you do have large sets of records there are alternative techniques that could be used that may be faster. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County ddoblank wrote: I have to columns that I want to try and filter out at the same time. Not really sure if this is possible. example Column 1 Column 2 770/005 K01001 770/005 K01002 770/005 K01003 770/006 W01001 770/006 K01004 770/007 Z01001 770/007 W01002 What I want to do is pull all of the column 1's that have only K values, then pull column 1's that have mixed k,w values and finally column 1's with mixed w,z values. I have tried to think off an IIF statement but couldn't quite get my head around it. I can't see how a Like or Not Like statement will work either. |
#4
|
|||
|
|||
Filtering between two columns
Wow, took me a while to wrap my head around that one, but I got it. Works
almost perfectly. The only problem I am having is, one of the queries is returning a value that wasn't looking for. When I write out the second statement you have below, it is returning all the K values from the first statement as well. Not sure if that is suppose to happen or not. We are asking for all K's and W's after all. Anyway, what I have works fantastic. I appreciate the help and if you have an idea about the above problem I would love to try it. Thanks again Darren "John W. Vinson" wrote: On Sat, 17 Apr 2010 09:55:01 -0700, ddoblank wrote: I have to columns that I want to try and filter out at the same time. Not really sure if this is possible. example Column 1 Column 2 770/005 K01001 770/005 K01002 770/005 K01003 770/006 W01001 770/006 K01004 770/007 Z01001 770/007 W01002 What I want to do is pull all of the column 1's that have only K values, then pull column 1's that have mixed k,w values and finally column 1's with mixed w,z values. I have tried to think off an IIF statement but couldn't quite get my head around it. I can't see how a Like or Not Like statement will work either. It sounds like you need three queries, not one: SELECT Column1 FROM tablename WHERE Column2 LIKE "K*" AND Column1 NOT IN (SELECT Column1 FROM tablename AS X WHERE X.Column1 = Table1.Column1 AND X.Column2 NOT LIKE "K*") SELECT Column1 FROM tablename WHERE Column2 LIKE "[KW]*" AND Column1 NOT IN (SELECT Column1 FROM tablename AS X WHERE X.Column1 = Table1.Column1 AND X.Column2 NOT LIKE "[KW]*") SELECT Column1 FROM tablename WHERE Column2 LIKE "[WZ]*" AND Column1 NOT IN (SELECT Column1 FROM tablename AS X WHERE X.Column1 = Table1.Column1 AND X.Column2 NOT LIKE "[WZ]*") -- John W. Vinson [MVP] . |
#5
|
|||
|
|||
Filtering between two columns
Hi John,
In your statements below, did you mean to enter [Column 2] instead of 1. All of the K's and W's are in column 2 so not sure how this works. Either way, I did try it both ways and I didn't get any results. I am mainly looking at the second statement for now. This is the one that is causing me the most headaches. I have copy clipped the SQL statement to show you how it was written and column 1 has been replaced by Skid Mix and column 2 would be replaced by PO if I had left it. SELECT qrySeneca_Skid_Sorting_Mixed.[Skid Mix] FROM qrySeneca_Skid_Sorting_Mixed WHERE (((Exists (Select * FROM [qrySeneca_Skid_Sorting_Mixed] as Temp WHERE [Skid Mix] LIKE "K*"))False) AND ((Exists (Select * FROM [qrySeneca_Skid_Sorting_Mixed] as Temp WHERE [Skid Mix] Like "W*"))False) AND NOT ((Exists (Select * FROM [qrySeneca_Skid_Sorting_Mixed] as Temp Where [Skid Mix] NOT LIKE "K*" and [Skid Mix] NOT LIKE "W*"))=False)); This is how it appears after I typed it in the way you have it below. What I do want to see in this query are only the skid mixes that have a K and a W value. I don't want to see any other skids that may contain just K's or W's. Thanks Darren "John Spencer" wrote: I think you want just those column 1 values that have a column 2 value that begins with K and has no column 2 values that begin with any other letter. SELECT Distinct [Column 1] FROM [TheTable] WHERE [Column 2] Like "K*" AND NOT EXISTS (SELECT * FROM [TheTable] as Temp WHERE [Column 1] NOT LIKE "K*") Next you want records have a column 2 with a K value and a W value. You did not say if you want that limited to only K and W and if they have a Z that is ok. SELECT [Column 1] FROM [TheTable] WHERE Exists (SELECT * FROM [TheTable] as Temp WHERE [Column 1] LIKE "K*") AND Exists (SELECT * FROM [TheTable] as Temp WHERE [Column 1] LIKE "W*") AND NOT Exists (SELECT * FROM [TheTable] as Temp WHERE [Column 1] Not LIKE "K*" AND [Column 1] Not Like "W*") Your third query should be similar and I leave it to you to figure it out. This WILL be SLOW if you have any large set of records. If you do have large sets of records there are alternative techniques that could be used that may be faster. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County ddoblank wrote: I have to columns that I want to try and filter out at the same time. Not really sure if this is possible. example Column 1 Column 2 770/005 K01001 770/005 K01002 770/005 K01003 770/006 W01001 770/006 K01004 770/007 Z01001 770/007 W01002 What I want to do is pull all of the column 1's that have only K values, then pull column 1's that have mixed k,w values and finally column 1's with mixed w,z values. I have tried to think off an IIF statement but couldn't quite get my head around it. I can't see how a Like or Not Like statement will work either. . |
#6
|
|||
|
|||
Filtering between two columns
On Sat, 17 Apr 2010 13:26:01 -0700, ddoblank
wrote: When I write out the second statement you have below, it is returning all the K values from the first statement as well. Not sure if that is suppose to happen or not. We are asking for all K's and W's after all. Anyway, what I have works fantastic. I appreciate the help and if you have an idea about the above problem I would love to try it. Well, maybe I misinterpreted it: If a record has (K) then it ipso facto has (K or W). If you want the second query to include only those rows which have at least one K and at least one W, and to exclude rows which have anything other than K or W, try SELECT Column1 FROM tablename WHERE Column2 LIKE "[KW]*" AND Column1 IN (SELECT Column1 FROM tablename AS X WHERE X.Column1 = Table1.Column1 AND X.Column2 LIKE "K*") AND Column1 IN (SELECT Column1 FROM tablename AS X WHERE X.Column1 = Table1.Column1 AND X.Column2 NOT LIKE "W*") AND Column1 NOT IN (SELECT Column1 FROM tablename AS X WHERE X.Column1 = Table1.Column1 AND X.Column2 NOT LIKE "[KW]*") As John Spencer says this is going to be pretty slow if your tables are at all big... -- John W. Vinson [MVP] |
#7
|
|||
|
|||
Filtering between two columns
Yes. I should have been testing column 2 not column 1.
SELECT qrySeneca_Skid_Sorting_Mixed.[Skid Mix] FROM qrySeneca_Skid_Sorting_Mixed WHERE Exists (Select * FROM [qrySeneca_Skid_Sorting_Mixed] as Temp WHERE [PO] LIKE "K*") AND Exists (Select * FROM [qrySeneca_Skid_Sorting_Mixed] as Temp WHERE [PO] Like "W*") That should return all records that have both a K and a W IF you have the possibility of having other values besides K and W for any Skid Mix then you need to expand the criteria AND NOT Exists (Select * FROM [qrySeneca_Skid_Sorting_Mixed] as Temp Where [PO] NOT LIKE "[KW]*") John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County ddoblank wrote: Hi John, In your statements below, did you mean to enter [Column 2] instead of 1. All of the K's and W's are in column 2 so not sure how this works. Either way, I did try it both ways and I didn't get any results. I am mainly looking at the second statement for now. This is the one that is causing me the most headaches. I have copy clipped the SQL statement to show you how it was written and column 1 has been replaced by Skid Mix and column 2 would be replaced by PO if I had left it. SELECT qrySeneca_Skid_Sorting_Mixed.[Skid Mix] FROM qrySeneca_Skid_Sorting_Mixed WHERE (((Exists (Select * FROM [qrySeneca_Skid_Sorting_Mixed] as Temp WHERE [Skid Mix] LIKE "K*"))False) AND ((Exists (Select * FROM [qrySeneca_Skid_Sorting_Mixed] as Temp WHERE [Skid Mix] Like "W*"))False) AND NOT ((Exists (Select * FROM [qrySeneca_Skid_Sorting_Mixed] as Temp Where [Skid Mix] NOT LIKE "K*" and [Skid Mix] NOT LIKE "W*"))=False)); This is how it appears after I typed it in the way you have it below. What I do want to see in this query are only the skid mixes that have a K and a W value. I don't want to see any other skids that may contain just K's or W's. Thanks Darren "John Spencer" wrote: I think you want just those column 1 values that have a column 2 value that begins with K and has no column 2 values that begin with any other letter. SELECT Distinct [Column 1] FROM [TheTable] WHERE [Column 2] Like "K*" AND NOT EXISTS (SELECT * FROM [TheTable] as Temp WHERE [Column 1] NOT LIKE "K*") Next you want records have a column 2 with a K value and a W value. You did not say if you want that limited to only K and W and if they have a Z that is ok. SELECT [Column 1] FROM [TheTable] WHERE Exists (SELECT * FROM [TheTable] as Temp WHERE [Column 1] LIKE "K*") AND Exists (SELECT * FROM [TheTable] as Temp WHERE [Column 1] LIKE "W*") AND NOT Exists (SELECT * FROM [TheTable] as Temp WHERE [Column 1] Not LIKE "K*" AND [Column 1] Not Like "W*") Your third query should be similar and I leave it to you to figure it out. This WILL be SLOW if you have any large set of records. If you do have large sets of records there are alternative techniques that could be used that may be faster. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County ddoblank wrote: I have to columns that I want to try and filter out at the same time. Not really sure if this is possible. example Column 1 Column 2 770/005 K01001 770/005 K01002 770/005 K01003 770/006 W01001 770/006 K01004 770/007 Z01001 770/007 W01002 What I want to do is pull all of the column 1's that have only K values, then pull column 1's that have mixed k,w values and finally column 1's with mixed w,z values. I have tried to think off an IIF statement but couldn't quite get my head around it. I can't see how a Like or Not Like statement will work either. . |
#8
|
|||
|
|||
Filtering between two columns
Thank both John's for all your help. Fortunately the tables that I am pull
this query from will never have a lot of data, so it runs pretty fast and very well too. Thanks again Darren "John Spencer" wrote: Yes. I should have been testing column 2 not column 1. SELECT qrySeneca_Skid_Sorting_Mixed.[Skid Mix] FROM qrySeneca_Skid_Sorting_Mixed WHERE Exists (Select * FROM [qrySeneca_Skid_Sorting_Mixed] as Temp WHERE [PO] LIKE "K*") AND Exists (Select * FROM [qrySeneca_Skid_Sorting_Mixed] as Temp WHERE [PO] Like "W*") That should return all records that have both a K and a W IF you have the possibility of having other values besides K and W for any Skid Mix then you need to expand the criteria AND NOT Exists (Select * FROM [qrySeneca_Skid_Sorting_Mixed] as Temp Where [PO] NOT LIKE "[KW]*") John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County ddoblank wrote: Hi John, In your statements below, did you mean to enter [Column 2] instead of 1. All of the K's and W's are in column 2 so not sure how this works. Either way, I did try it both ways and I didn't get any results. I am mainly looking at the second statement for now. This is the one that is causing me the most headaches. I have copy clipped the SQL statement to show you how it was written and column 1 has been replaced by Skid Mix and column 2 would be replaced by PO if I had left it. SELECT qrySeneca_Skid_Sorting_Mixed.[Skid Mix] FROM qrySeneca_Skid_Sorting_Mixed WHERE (((Exists (Select * FROM [qrySeneca_Skid_Sorting_Mixed] as Temp WHERE [Skid Mix] LIKE "K*"))False) AND ((Exists (Select * FROM [qrySeneca_Skid_Sorting_Mixed] as Temp WHERE [Skid Mix] Like "W*"))False) AND NOT ((Exists (Select * FROM [qrySeneca_Skid_Sorting_Mixed] as Temp Where [Skid Mix] NOT LIKE "K*" and [Skid Mix] NOT LIKE "W*"))=False)); This is how it appears after I typed it in the way you have it below. What I do want to see in this query are only the skid mixes that have a K and a W value. I don't want to see any other skids that may contain just K's or W's. Thanks Darren "John Spencer" wrote: I think you want just those column 1 values that have a column 2 value that begins with K and has no column 2 values that begin with any other letter. SELECT Distinct [Column 1] FROM [TheTable] WHERE [Column 2] Like "K*" AND NOT EXISTS (SELECT * FROM [TheTable] as Temp WHERE [Column 1] NOT LIKE "K*") Next you want records have a column 2 with a K value and a W value. You did not say if you want that limited to only K and W and if they have a Z that is ok. SELECT [Column 1] FROM [TheTable] WHERE Exists (SELECT * FROM [TheTable] as Temp WHERE [Column 1] LIKE "K*") AND Exists (SELECT * FROM [TheTable] as Temp WHERE [Column 1] LIKE "W*") AND NOT Exists (SELECT * FROM [TheTable] as Temp WHERE [Column 1] Not LIKE "K*" AND [Column 1] Not Like "W*") Your third query should be similar and I leave it to you to figure it out. This WILL be SLOW if you have any large set of records. If you do have large sets of records there are alternative techniques that could be used that may be faster. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County ddoblank wrote: I have to columns that I want to try and filter out at the same time. Not really sure if this is possible. example Column 1 Column 2 770/005 K01001 770/005 K01002 770/005 K01003 770/006 W01001 770/006 K01004 770/007 Z01001 770/007 W01002 What I want to do is pull all of the column 1's that have only K values, then pull column 1's that have mixed k,w values and finally column 1's with mixed w,z values. I have tried to think off an IIF statement but couldn't quite get my head around it. I can't see how a Like or Not Like statement will work either. . . |
Thread Tools | |
Display Modes | |
|
|