Query - Omit records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to build a query to omit all records that two different controls,
but I am not sure how to do it. Could someone step me through it? Example:
IIf(Consultant Type = "Outsourced" & Vendor Name = "IBM", don't show record)
 
While in design view of your query, in the criteria under Cunsultant Type
put...

Not Outsourced

under the Vendor Name put...

Not IBM



Rick B

troubled said:
I am trying to build a query to omit all records that two different controls,
but I am not sure how to do it. Could someone step me through it? Example:
IIf(Consultant Type = "Outsourced" & Vendor Name = "IBM", don't show
Click to expand...
record)
 
Rick,
Thank you for your help, but I was able to create a new field were I combine
both Consultant Type + Vendor Name. Then I entered into the critera Not
OutsourcedIBM. That worked perfectly.
Thank you again for helping.
Have a great day.
 
Well troubled, I'm no expert but I don't think this is as simple as Rick is
suggesting. I think his criteria will eliminate
all records that have EITHER IBM as a vendor or consultant type as
Outsourced and you are only interested in
eliminating records where BOTH are true. As I said, I am no expert, so I
tend to come up with Rube Goldbergesque
solutions. I make the mechanisms that I understand work, even if it means an
inordinate amount of steps. In your case, I would create
an expression in my query ......Iif([ConsultantType]<>"Outsourced",1,0)
then another expression (yes, another column) .....Iif([VendorName]<>"IBM",
1,0)
then another expression (let's call it Mary) that adds Expr1 to Expr2
Then another query that calls up all the values in this first query except
in the criteria for Mary enter >0

As I said, simple elegant solutions are not my forte. I work with what
little I understand and go from there.
Hope this helps.

Ed G



troubled said:
I am trying to build a query to omit all records that two different controls,
but I am not sure how to do it. Could someone step me through it? Example:
IIf(Consultant Type = "Outsourced" & Vendor Name = "IBM", don't show
Click to expand...
record)
 
Wrong.

Ccriteria on one line form an "AND" operation (both must be true). Placing
them on two lines forms an "OR" operation (either must match)


Edward G said:
Well troubled, I'm no expert but I don't think this is as simple as Rick is
suggesting. I think his criteria will eliminate
all records that have EITHER IBM as a vendor or consultant type as
Outsourced and you are only interested in
eliminating records where BOTH are true. As I said, I am no expert, so I
tend to come up with Rube Goldbergesque
solutions. I make the mechanisms that I understand work, even if it means an
inordinate amount of steps. In your case, I would create
an expression in my query ......Iif([ConsultantType]<>"Outsourced",1,0)
then another expression (yes, another column)
Click to expand...
 
no need to create a new field.


troubled said:
Rick,
Thank you for your help, but I was able to create a new field were I combine
both Consultant Type + Vendor Name. Then I entered into the critera Not
OutsourcedIBM. That worked perfectly.
Thank you again for helping.
Have a great day.
Click to expand...
 
Then Rick, how do explain the fact that your suggestion does not work?

Ed


Rick B said:
Wrong.

Ccriteria on one line form an "AND" operation (both must be true). Placing
them on two lines forms an "OR" operation (either must match)


Edward G said:
Well troubled, I'm no expert but I don't think this is as simple as Rick is
suggesting. I think his criteria will eliminate
all records that have EITHER IBM as a vendor or consultant type as
Outsourced and you are only interested in
eliminating records where BOTH are true. As I said, I am no expert, so I
tend to come up with Rube Goldbergesque
solutions. I make the mechanisms that I understand work, even if it
Click to expand...
means
an
inordinate amount of steps. In your case, I would create
an expression in my query ......Iif([ConsultantType]<>"Outsourced",1,0)
then another expression (yes, another column)
Click to expand...
.....Iif( said:
1,0)
then another expression (let's call it Mary) that adds Expr1 to Expr2
Then another query that calls up all the values in this first query except
in the criteria for Mary enter >0

As I said, simple elegant solutions are not my forte. I work with what
little I understand and go from there.
Hope this helps.

Ed G




record)
Click to expand...
Click to expand...
 
Placing the criteria on one line will look for cases where both conditions
are met. Placing on two lines will look for cases where either condition is
met. this is basic query stuff here. Nothing fancy.

Rick b
 
Rick,

I would suggest you actually try your suggestion out. I think you will find
that when
you use NOT in criteria it complicates things.

Ed


Rick B said:
Placing the criteria on one line will look for cases where both conditions
are met. Placing on two lines will look for cases where either condition is
met. this is basic query stuff here. Nothing fancy.

Rick b



Edward G said:
Then Rick, how do explain the fact that your suggestion does not work?

Ed
Click to expand...
so
I
tend to come up with Rube Goldbergesque
solutions. I make the mechanisms that I understand work, even if it means
an
inordinate amount of steps. In your case, I would create
an expression in my query
Click to expand...
......Iif( said:
then another expression (yes, another column)
.....Iif([VendorName]<>"IBM",
1,0)
then another expression (let's call it Mary) that adds Expr1 to Expr2
Then another query that calls up all the values in this first query except
in the criteria for Mary enter >0

As I said, simple elegant solutions are not my forte. I work with what
little I understand and go from there.
Hope this helps.

Ed G



I am trying to build a query to omit all records that two different
controls,
but I am not sure how to do it. Could someone step me through it?
Example:
IIf(Consultant Type = "Outsourced" & Vendor Name = "IBM", don't show
record)
Click to expand...
Click to expand...
Click to expand...
 
Using the Northwinds database I created a query of all products. I used the
exact method mentioned to exclude all products of type "beverage" and
supplier "Mayumi's".

As previously stated, my original post was correct and works as stated.
Here is the SQL if you need to see it. You can create that in a query and
then view it in design view and you will see the exact method I outlined.




SELECT Products.ProductID, Products.ProductName, Suppliers.CompanyName,
Products.SupplierID, Categories.CategoryName, Products.CategoryID,
Products.QuantityPerUnit, Products.UnitPrice, Products.UnitsInStock,
Products.UnitsOnOrder, Products.ReorderLevel, Products.Discontinued
FROM Categories INNER JOIN (Suppliers INNER JOIN Products ON
Suppliers.SupplierID = Products.SupplierID) ON Categories.CategoryID =
Products.CategoryID
WHERE ((Not (Suppliers.CompanyName)="Mayumi's") AND (Not
(Categories.CategoryName)="Beverages"));


Rick B



Edward G said:
Rick,

I would suggest you actually try your suggestion out. I think you will find
that when
you use NOT in criteria it complicates things.

Ed


Rick B said:
Placing the criteria on one line will look for cases where both conditions
are met. Placing on two lines will look for cases where either
Click to expand...
condition
is
met. this is basic query stuff here. Nothing fancy.

Rick b



as
Rick
Click to expand...
expert,
so
I
tend to come up with Rube Goldbergesque
solutions. I make the mechanisms that I understand work, even if it
means
an
inordinate amount of steps. In your case, I would create
an expression in my query
Click to expand...
......Iif( said:
then another expression (yes, another column)
.....Iif([VendorName]<>"IBM",
1,0)
then another expression (let's call it Mary) that adds Expr1 to Expr2
Then another query that calls up all the values in this first query
except
in the criteria for Mary enter >0

As I said, simple elegant solutions are not my forte. I work with what
little I understand and go from there.
Hope this helps.

Ed G



I am trying to build a query to omit all records that two different
controls,
but I am not sure how to do it. Could someone step me through it?
Example:
IIf(Consultant Type = "Outsourced" & Vendor Name = "IBM", don't show
record)
Click to expand...
Click to expand...
Click to expand...
 
Nevermind. I am soo sorry. I looked at this further and see that this is
wrong.

Wht's really bad, is that I was using this logic on my database at
work!!!?!?

Sorry. I was so confident because we use a query like this everyday. In
our case it is almost correct because the two fields we are testing almost
always go hand-in-hand. I did find two cases where it is wrong though.

Thanks for pointing this out.


Rick B said:
Using the Northwinds database I created a query of all products. I used the
exact method mentioned to exclude all products of type "beverage" and
supplier "Mayumi's".

As previously stated, my original post was correct and works as stated.
Here is the SQL if you need to see it. You can create that in a query and
then view it in design view and you will see the exact method I outlined.




SELECT Products.ProductID, Products.ProductName, Suppliers.CompanyName,
Products.SupplierID, Categories.CategoryName, Products.CategoryID,
Products.QuantityPerUnit, Products.UnitPrice, Products.UnitsInStock,
Products.UnitsOnOrder, Products.ReorderLevel, Products.Discontinued
FROM Categories INNER JOIN (Suppliers INNER JOIN Products ON
Suppliers.SupplierID = Products.SupplierID) ON Categories.CategoryID =
Products.CategoryID
WHERE ((Not (Suppliers.CompanyName)="Mayumi's") AND (Not
(Categories.CategoryName)="Beverages"));


Rick B



Edward G said:
Rick,

I would suggest you actually try your suggestion out. I think you will find
that when
you use NOT in criteria it complicates things.

Ed


Rick B said:
Placing the criteria on one line will look for cases where both conditions
are met. Placing on two lines will look for cases where either
Click to expand...
condition
is
met. this is basic query stuff here. Nothing fancy.

Rick b



Then Rick, how do explain the fact that your suggestion does not work?

Ed


"Rick B" <Anonymous> wrote in message
Wrong.

Ccriteria on one line form an "AND" operation (both must be true).
Placing
them on two lines forms an "OR" operation (either must match)


Well troubled, I'm no expert but I don't think this is as simple as
Rick
is
suggesting. I think his criteria will eliminate
all records that have EITHER IBM as a vendor or consultant type as
Outsourced and you are only interested in
eliminating records where BOTH are true. As I said, I am no
Click to expand...
expert,
so
I
tend to come up with Rube Goldbergesque
solutions. I make the mechanisms that I understand work, even if it
means
an
inordinate amount of steps. In your case, I would create
an expression in my query
......Iif([ConsultantType]<>"Outsourced",1,0)
then another expression (yes, another column)
.....Iif([VendorName]<>"IBM",
1,0)
then another expression (let's call it Mary) that adds Expr1 to Expr2
Then another query that calls up all the values in this first query
except
in the criteria for Mary enter >0

As I said, simple elegant solutions are not my forte. I work
Click to expand...
with
what
little I understand and go from there.
Hope this helps.

Ed G



I am trying to build a query to omit all records that two different
controls,
but I am not sure how to do it. Could someone step me through it?
Example:
IIf(Consultant Type = "Outsourced" & Vendor Name = "IBM",
Click to expand...
don't
show
record)
Click to expand...
Click to expand...
Click to expand...
 
No Rick. I tried your SQL and it does NOT work as desired. Notice that run
as you designed it there are NO records for ANY Vendor supplying Beverages
and NO records for ANY products supplied by Mayumi. That is not the result
we are looking for.

Ed





Rick B said:
Using the Northwinds database I created a query of all products. I used the
exact method mentioned to exclude all products of type "beverage" and
supplier "Mayumi's".

As previously stated, my original post was correct and works as stated.
Here is the SQL if you need to see it. You can create that in a query and
then view it in design view and you will see the exact method I outlined.




SELECT Products.ProductID, Products.ProductName, Suppliers.CompanyName,
Products.SupplierID, Categories.CategoryName, Products.CategoryID,
Products.QuantityPerUnit, Products.UnitPrice, Products.UnitsInStock,
Products.UnitsOnOrder, Products.ReorderLevel, Products.Discontinued
FROM Categories INNER JOIN (Suppliers INNER JOIN Products ON
Suppliers.SupplierID = Products.SupplierID) ON Categories.CategoryID =
Products.CategoryID
WHERE ((Not (Suppliers.CompanyName)="Mayumi's") AND (Not
(Categories.CategoryName)="Beverages"));


Rick B



Edward G said:
Rick,

I would suggest you actually try your suggestion out. I think you will find
that when
you use NOT in criteria it complicates things.

Ed


Rick B said:
Placing the criteria on one line will look for cases where both conditions
are met. Placing on two lines will look for cases where either
Click to expand...
condition
is
met. this is basic query stuff here. Nothing fancy.

Rick b



Then Rick, how do explain the fact that your suggestion does not work?

Ed


"Rick B" <Anonymous> wrote in message
Wrong.

Ccriteria on one line form an "AND" operation (both must be true).
Placing
them on two lines forms an "OR" operation (either must match)


Well troubled, I'm no expert but I don't think this is as simple as
Rick
is
suggesting. I think his criteria will eliminate
all records that have EITHER IBM as a vendor or consultant type as
Outsourced and you are only interested in
eliminating records where BOTH are true. As I said, I am no
Click to expand...
expert,
so
I
tend to come up with Rube Goldbergesque
solutions. I make the mechanisms that I understand work, even if it
means
an
inordinate amount of steps. In your case, I would create
an expression in my query
......Iif([ConsultantType]<>"Outsourced",1,0)
then another expression (yes, another column)
.....Iif([VendorName]<>"IBM",
1,0)
then another expression (let's call it Mary) that adds Expr1 to Expr2
Then another query that calls up all the values in this first query
except
in the criteria for Mary enter >0

As I said, simple elegant solutions are not my forte. I work
Click to expand...
with
what
little I understand and go from there.
Hope this helps.

Ed G



I am trying to build a query to omit all records that two different
controls,
but I am not sure how to do it. Could someone step me through it?
Example:
IIf(Consultant Type = "Outsourced" & Vendor Name = "IBM",
Click to expand...
don't
show
record)
Click to expand...
Click to expand...
Click to expand...
 
Rick,

I believe you were on the right track, but just had it a little off. I
think this one is easier to think about in the reverse first. If you wanted
only the records where:

([Consultant Type] = "Outsourced" AND [Vendor Name] = "IBM")

that's pretty straightforward, and the result of the expression in the ()
would be true only for IBM Outsourced records. So, if you want to exclude
those records (and only those), you just need to modify the where condition
to:

NOT ([Consultant Type] = "Outsourced" AND [Vendor Name] = "IBM")

Since NOT changes a boolean's value, the only records that will return false
would be those that previously returned true (Outsourced IBM).

I think that the earlier error was that you were using NOT in front of each
part of the expression, and not the expression as a whole.

In the query builder, this would be the equivalent of typing a calculated
field value of:

[Consultant Type] = "Outsourced" AND [Vendor Name] = "IBM"

and then typing False as the criteria.

-Ted Allen

Rick B said:
Using the Northwinds database I created a query of all products. I used the
exact method mentioned to exclude all products of type "beverage" and
supplier "Mayumi's".

As previously stated, my original post was correct and works as stated.
Here is the SQL if you need to see it. You can create that in a query and
then view it in design view and you will see the exact method I outlined.




SELECT Products.ProductID, Products.ProductName, Suppliers.CompanyName,
Products.SupplierID, Categories.CategoryName, Products.CategoryID,
Products.QuantityPerUnit, Products.UnitPrice, Products.UnitsInStock,
Products.UnitsOnOrder, Products.ReorderLevel, Products.Discontinued
FROM Categories INNER JOIN (Suppliers INNER JOIN Products ON
Suppliers.SupplierID = Products.SupplierID) ON Categories.CategoryID =
Products.CategoryID
WHERE ((Not (Suppliers.CompanyName)="Mayumi's") AND (Not
(Categories.CategoryName)="Beverages"));


Rick B



Edward G said:
Rick,

I would suggest you actually try your suggestion out. I think you will find
that when
you use NOT in criteria it complicates things.

Ed


Rick B said:
Placing the criteria on one line will look for cases where both conditions
are met. Placing on two lines will look for cases where either
Click to expand...
condition
is
met. this is basic query stuff here. Nothing fancy.

Rick b



Then Rick, how do explain the fact that your suggestion does not work?

Ed


"Rick B" <Anonymous> wrote in message
Wrong.

Ccriteria on one line form an "AND" operation (both must be true).
Placing
them on two lines forms an "OR" operation (either must match)


Well troubled, I'm no expert but I don't think this is as simple as
Rick
is
suggesting. I think his criteria will eliminate
all records that have EITHER IBM as a vendor or consultant type as
Outsourced and you are only interested in
eliminating records where BOTH are true. As I said, I am no
Click to expand...
expert,
so
I
tend to come up with Rube Goldbergesque
solutions. I make the mechanisms that I understand work, even if it
means
an
inordinate amount of steps. In your case, I would create
an expression in my query
......Iif([ConsultantType]<>"Outsourced",1,0)
then another expression (yes, another column)
.....Iif([VendorName]<>"IBM",
1,0)
then another expression (let's call it Mary) that adds Expr1 to Expr2
Then another query that calls up all the values in this first query
except
in the criteria for Mary enter >0

As I said, simple elegant solutions are not my forte. I work with what
little I understand and go from there.
Hope this helps.

Ed G



I am trying to build a query to omit all records that two different
controls,
but I am not sure how to do it. Could someone step me through it?
Example:
IIf(Consultant Type = "Outsourced" & Vendor Name = "IBM", don't show
record)
Click to expand...
Click to expand...
Click to expand...
 
Thanks, Ted!

Rick
Ted Allen said:
Rick,

I believe you were on the right track, but just had it a little off. I
think this one is easier to think about in the reverse first. If you wanted
only the records where:

([Consultant Type] = "Outsourced" AND [Vendor Name] = "IBM")

that's pretty straightforward, and the result of the expression in the ()
would be true only for IBM Outsourced records. So, if you want to exclude
those records (and only those), you just need to modify the where condition
to:

NOT ([Consultant Type] = "Outsourced" AND [Vendor Name] = "IBM")

Since NOT changes a boolean's value, the only records that will return false
would be those that previously returned true (Outsourced IBM).

I think that the earlier error was that you were using NOT in front of each
part of the expression, and not the expression as a whole.

In the query builder, this would be the equivalent of typing a calculated
field value of:

[Consultant Type] = "Outsourced" AND [Vendor Name] = "IBM"

and then typing False as the criteria.

-Ted Allen

Rick B said:
Using the Northwinds database I created a query of all products. I used the
exact method mentioned to exclude all products of type "beverage" and
supplier "Mayumi's".

As previously stated, my original post was correct and works as stated.
Here is the SQL if you need to see it. You can create that in a query and
then view it in design view and you will see the exact method I outlined.




SELECT Products.ProductID, Products.ProductName, Suppliers.CompanyName,
Products.SupplierID, Categories.CategoryName, Products.CategoryID,
Products.QuantityPerUnit, Products.UnitPrice, Products.UnitsInStock,
Products.UnitsOnOrder, Products.ReorderLevel, Products.Discontinued
FROM Categories INNER JOIN (Suppliers INNER JOIN Products ON
Suppliers.SupplierID = Products.SupplierID) ON Categories.CategoryID =
Products.CategoryID
WHERE ((Not (Suppliers.CompanyName)="Mayumi's") AND (Not
(Categories.CategoryName)="Beverages"));


Rick B



Edward G said:
Rick,

I would suggest you actually try your suggestion out. I think you will find
that when
you use NOT in criteria it complicates things.

Ed


"Rick B" <Anonymous> wrote in message

Placing the criteria on one line will look for cases where both conditions
are met. Placing on two lines will look for cases where either condition
is
met. this is basic query stuff here. Nothing fancy.

Rick b



Then Rick, how do explain the fact that your suggestion does not work?

Ed


"Rick B" <Anonymous> wrote in message
Wrong.

Ccriteria on one line form an "AND" operation (both must be true).
Placing
them on two lines forms an "OR" operation (either must match)


Well troubled, I'm no expert but I don't think this is as
Click to expand...
simple
as
Rick
is
suggesting. I think his criteria will eliminate
all records that have EITHER IBM as a vendor or consultant type as
Outsourced and you are only interested in
eliminating records where BOTH are true. As I said, I am no expert,
so
I
tend to come up with Rube Goldbergesque
solutions. I make the mechanisms that I understand work, even
Click to expand...
if
it
means
an
inordinate amount of steps. In your case, I would create
an expression in my query
......Iif([ConsultantType]<>"Outsourced",1,0)
then another expression (yes, another column)
.....Iif([VendorName]<>"IBM",
1,0)
then another expression (let's call it Mary) that adds Expr1 to
Expr2
Then another query that calls up all the values in this first query
except
in the criteria for Mary enter >0

As I said, simple elegant solutions are not my forte. I work with
what
little I understand and go from there.
Hope this helps.

Ed G



I am trying to build a query to omit all records that two
different
controls,
but I am not sure how to do it. Could someone step me
Click to expand...
through
it?
Example:
IIf(Consultant Type = "Outsourced" & Vendor Name = "IBM", don't
show
record)
Click to expand...
Click to expand...
Click to expand...
 
My Pleasure.

Rick B said:
Thanks, Ted!

Rick
Ted Allen said:
Rick,

I believe you were on the right track, but just had it a little off. I
think this one is easier to think about in the reverse first. If you wanted
only the records where:

([Consultant Type] = "Outsourced" AND [Vendor Name] = "IBM")

that's pretty straightforward, and the result of the expression in the ()
would be true only for IBM Outsourced records. So, if you want to exclude
those records (and only those), you just need to modify the where condition
to:

NOT ([Consultant Type] = "Outsourced" AND [Vendor Name] = "IBM")

Since NOT changes a boolean's value, the only records that will return false
would be those that previously returned true (Outsourced IBM).

I think that the earlier error was that you were using NOT in front of each
part of the expression, and not the expression as a whole.

In the query builder, this would be the equivalent of typing a calculated
field value of:

[Consultant Type] = "Outsourced" AND [Vendor Name] = "IBM"

and then typing False as the criteria.

-Ted Allen

Rick B said:
Using the Northwinds database I created a query of all products. I used the
exact method mentioned to exclude all products of type "beverage" and
supplier "Mayumi's".

As previously stated, my original post was correct and works as stated.
Here is the SQL if you need to see it. You can create that in a query and
then view it in design view and you will see the exact method I outlined.




SELECT Products.ProductID, Products.ProductName, Suppliers.CompanyName,
Products.SupplierID, Categories.CategoryName, Products.CategoryID,
Products.QuantityPerUnit, Products.UnitPrice, Products.UnitsInStock,
Products.UnitsOnOrder, Products.ReorderLevel, Products.Discontinued
FROM Categories INNER JOIN (Suppliers INNER JOIN Products ON
Suppliers.SupplierID = Products.SupplierID) ON Categories.CategoryID =
Products.CategoryID
WHERE ((Not (Suppliers.CompanyName)="Mayumi's") AND (Not
(Categories.CategoryName)="Beverages"));


Rick B



Rick,

I would suggest you actually try your suggestion out. I think you will
find
that when
you use NOT in criteria it complicates things.

Ed


"Rick B" <Anonymous> wrote in message

Placing the criteria on one line will look for cases where both
conditions
are met. Placing on two lines will look for cases where either
condition
is
met. this is basic query stuff here. Nothing fancy.

Rick b



Then Rick, how do explain the fact that your suggestion does not work?

Ed


"Rick B" <Anonymous> wrote in message
Wrong.

Ccriteria on one line form an "AND" operation (both must be true).
Placing
them on two lines forms an "OR" operation (either must match)


Well troubled, I'm no expert but I don't think this is as simple
as
Rick
is
suggesting. I think his criteria will eliminate
all records that have EITHER IBM as a vendor or consultant type as
Outsourced and you are only interested in
eliminating records where BOTH are true. As I said, I am no
expert,
so
I
tend to come up with Rube Goldbergesque
solutions. I make the mechanisms that I understand work, even if
it
means
an
inordinate amount of steps. In your case, I would create
an expression in my query
......Iif([ConsultantType]<>"Outsourced",1,0)
then another expression (yes, another column)
.....Iif([VendorName]<>"IBM",
1,0)
then another expression (let's call it Mary) that adds Expr1 to
Expr2
Then another query that calls up all the values in this first
query
except
in the criteria for Mary enter >0

As I said, simple elegant solutions are not my forte. I work with
what
little I understand and go from there.
Hope this helps.

Ed G



I am trying to build a query to omit all records that two
different
controls,
but I am not sure how to do it. Could someone step me through
it?
Example:
IIf(Consultant Type = "Outsourced" & Vendor Name = "IBM", don't
show
record)
Click to expand...
Click to expand...
Click to expand...
 
Back
Top