Re: Query - Omit records
From: Ted Allen (TedAllen_at_discussions.microsoft.com)
Date: 12/03/04
- Next message: Martin: "RE: Unmatched Query"
- Previous message: Carie: "how do I have Access display the same data for each day between a."
- In reply to: Rick B: "Re: Query - Omit records"
- Next in thread: Rick B: "Re: Query - Omit records"
- Reply: Rick B: "Re: Query - Omit records"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 3 Dec 2004 13:25:01 -0800
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" wrote:
> 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" <junkmail4ever@att.netREMOVE> wrote in message
> news:Hj1sd.10$oN3.8@fe10.lga...
> > 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
> > news:#lwtsqV2EHA.3368@TK2MSFTNGP10.phx.gbl...
> > >
> > > 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" <junkmail4ever@att.netREMOVE> wrote in message
> > > news:Ei0sd.7$a53.4@fe10.lga...
> > > > Then Rick, how do explain the fact that your suggestion does not work?
> > > >
> > > > Ed
> > > >
> > > >
> > > > "Rick B" <Anonymous> wrote in message
> > > > news:O42dCKV2EHA.2568@TK2MSFTNGP11.phx.gbl...
> > > > > 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" <junkmail4ever@att.netREMOVE> wrote in message
> > > > > news:eY%rd.4522$Vu7.1334@fe12.lga...
> > > > > > 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" <troubled@discussions.microsoft.com> wrote in message
> > > > > > news:D3557F79-0962-4451-B131-C082050AF2A6@microsoft.com...
> > > > > > > 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)
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
>
- Next message: Martin: "RE: Unmatched Query"
- Previous message: Carie: "how do I have Access display the same data for each day between a."
- In reply to: Rick B: "Re: Query - Omit records"
- Next in thread: Rick B: "Re: Query - Omit records"
- Reply: Rick B: "Re: Query - Omit records"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
