Results 1 to 8 of 8
  1. #1
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: An Excluding Query (Any Version)

    I can think of two ways offhand.

    One is to create a separate query that includes everyone with an A, B or C. Then join that query with an outer join to only include those where the link is Null between the tables.

    The other is to create a subquery in your main query using the same select as you would for the first option but only returning the PersonID (or whatever) key. Then you could use Not In with the subquery expression in parens in the criteria for the PersonID field.
    Charlotte

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: An Excluding Query (Any Version)

    This is a sticky one. I'd guess you would have to have a subquery that counted all the invalid records for a particular person, then accepted that person only if the count was zero. Something like:

    Select count(*) from Trans Where Trans.custid=cust.custid
    AND trans.type in ('A', 'B', 'C')
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: An Excluding Query (Any Version)

    Where are you putting

    Select count(*) from Trans Where Trans.custid=cust.custid
    AND trans.type in ('A', 'B', 'C')

    -TIA

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: An Excluding Query (Any Version)

    I'm putting it as an expression in the "Field" row of the QBE grid. I would then set it selection criteria to =0. This should exclude any customer who has at least 1 transaction containing the verboten values.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Salem, Oregon, USA
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: An Excluding Query (Any Version)

    Select count(YourTable.PersonID) from YourTable
    Where YourTable.PersonID not in
    (Select subTable.PersonID
    from YourTable subTable
    where subTable.AlphaField in ('A', 'B', 'C'))

  6. #6
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts

    An Excluding Query (Any Version) - REVISITED

    Greetings All,

    How do you handle making an Excluding query?

    Due to the nature of the program my table of data is coming from, it's a flat file, so the Customer ID and Name Repeat for each record they have in the table, and it is a single table of data no child tables.

    Let's say Eileen has 5 records, but if any of her records has a value of A, B, or C in a particular field I want to exclude her name from the results, while let's say Charlotte has 9 records and no code of A, B, or C in the same field, so I want to include her. What do you all do to get your results?

    I hope I asked this clearly,

    TIA

  7. #7
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: An Excluding Query - GOT IT YEEHA!!!

    OK, Got it!

    Charlotte was right there with the use of IN and NOT all I had to do was Group it.

    SELECT myTable.myField
    FROM myTable
    GROUP BY myTable.myField
    HAVING (((myTable.myField) NOT IN (SELECT myField From myTable
    WHERE theFieldToTest IN("A","B","C"))));

    This worked very well. This solution was fast too! Compared to my original solution, which took quite a while.

    My original method of creating a calculated field with a value of true if in A,B, or C then setting the query to Unique values, took a long long time.

    Thanks Charlotte, that was the trick, I forgot about IN and the possibilities! Thanks to all who posted.

    As always, if anyone sees a major flaw in my thinking here, please post me in the right direction.

  8. #8
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: An Excluding Query (Any Version)

    Doesn't this only give me a count?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •