Filtering between two columns

  • Thread starter Thread starter ddoblank
  • Start date Start date
D

ddoblank

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.
 
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.
Click to expand...

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]*")
 
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
 
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 said:
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.
Click to expand...

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]*")
Click to expand...
 
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 said:
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
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.
Click to expand...
.
Click to expand...
 
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.
Click to expand...

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...
 
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
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 said:
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
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.
Click to expand...
.
Click to expand...
Click to expand...
 
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 said:
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
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 said:
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.
.
Click to expand...
Click to expand...
.
Click to expand...
 
Back
Top