Results 1 to 7 of 7
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query Criteria (XP)

    Is it possible to have query criteria exclude records based on another table?

    I have three tables, the first named "Data" where the field names are BUnit, Month and Amount. The second table named "All BUnits" (fields: BUnit, Descr) and finally the third table named "Exclude" (fields: BUnit,Descr). What I would like to do is query the DATA table and exclude those BUnits in the third table (EXCLUDE). The list of bunits in the third table is quite long therefore it does not make sense to list each one individually in the criteria.

    Thanks,
    John

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

    Re: Query Criteria (XP)

    Try something like this:

    SELECT * FROM DATA LEFT JOIN EXCLUDE On Data.BUnit = EXCLUDE.BUnit WHERE EXCLUDE.BUnit Is Null

    Another way to do it is with a subquery on EXCLUDE, but it would require the use of the NOT IN keywords, and would be much slower.

    SELECT * FROM DATA WHERE Data.BUnit Not In (SELECT BUnit FROM EXCLUDE)
    Charlotte

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Criteria (XP)

    Charlotte,

    I run into an issue where I can not return records if I tweak the following SQL code with your suggestions. I believe it relates to the Group By and Sum. Your suggestion works if I replace my code with yours.

    John
    Attached Images Attached Images

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Criteria (XP)

    Have you tried Charlotte's second option?

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Criteria (XP)

    Hans,

    I tweaked my code with Charlotte's second suggestion and it failed to return records. In fact, I received this error message. It has to do with the Sum(DATA.[Total Amt]) AS [SumofTotal Amt].

    Charlotte's code alone will return the valid records however my data table has 12 periods (Jan-Dec) the SUM of course tries to total the twelve periods and return the value for each account
    Attached Images Attached Images

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Criteria (XP)

    The query you showed in your previous reply is a Totals query - it has a Total: line, the SQL for that query has a GROUP BY clause.

    There is no GROUP BY in the SQL in your latest reply; you cannot have both non-aggregated fields (such as Dept) and aggregated fields (such as TotalAmount) in one query. You wil have to decide what you want: a Totals query or not.

  7. #7
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Criteria (XP)

    Hans,

    I didn't catch the GROUP BY when I was tweaking the SQL.

    Thanks, I got it to work.
    John

Posting Permissions

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