Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Feb 2003
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Creating a complicated exclusion using a Query (2000)

    Hello all,

    I am working on a project to analyze straight through and non-straight through processing rates of transactions through our system. As a result, I

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

    Re: Creating a complicated exclusion using a Query (2000)

    Say that your table is named tblImport.

    Here is the SQL for a query that selects all records for transactions with no Stop_Pay_Log log:

    SELECT * FROM tblImport WHERE TRN Not In (SELECT TRN FROM tblImport WHERE Log="Stop_Pay_Log")

    And to get all records for transactions with a Stop_Pay_Log log:

    SELECT * FROM tblImport WHERE TRN In (SELECT TRN FROM tblImport WHERE Log="Stop_Pay_Log")

  3. #3
    Star Lounger
    Join Date
    Feb 2003
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating a complicated exclusion using a Query (2000)

    Thanks Hans,

    I'm not all that familar with writing the actual SQL statement code - so I'm not sure where I should write this statement. Would I create a query - say qryimport - with each field represented, i.e. TRN, Log, Log Date, etc., and apply this statment on the criteria line for the TRN field while in Design View or would I need to go directly into SQL View and edit it there?

    This is what the SQL View code looks like now (which is a bit different than I originally described, as there are a few more fields - but shouldn't make a substantial difference):

    SELECT [DATA-NSTPtbl].PrimeKey, [DATA-NSTPtbl].TRN, [DATA-NSTPtbl].Bank, [DATA-NSTPtbl].Debit, [DATA-NSTPtbl].Credit, [DATA-NSTPtbl].Log, [DATA-NSTPtbl].[Log Date], [DATA-NSTPtbl].[Log Time]
    FROM [DATA-NSTPtbl]
    ORDER BY [DATA-NSTPtbl].PrimeKey;


    thanks again,

    Scott

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

    Re: Creating a complicated exclusion using a Query (2000)

    You can create the query in design view or in SQL view, just as you prefer.

    In design view, enter the following in the Criteria: line under TRN:

    Not In (SELECT TRN FROM [DATA-NSTPtbl] WHERE Log="Stop_Pay_Log")

    (omit Not if you want transactions that have a Stop_Pay_Log log)

    In SQL view, the query would look like this:

    SELECT [DATA-NSTPtbl].PrimeKey, [DATA-NSTPtbl].TRN, [DATA-NSTPtbl].Bank, [DATA-NSTPtbl].Debit, [DATA-NSTPtbl].Credit, [DATA-NSTPtbl].Log, [DATA-NSTPtbl].[Log Date], [DATA-NSTPtbl].[Log Time]
    FROM [DATA-NSTPtbl]
    WHERE [DATA-NSTPtbl].TRN Not In (SELECT TRN FROM [DATA-NSTPtbl] WHERE Log="Stop_Pay_Log")
    ORDER BY [DATA-NSTPtbl].PrimeKey;

    (again, omit Not if appropriate)

  5. #5
    Star Lounger
    Join Date
    Feb 2003
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating a complicated exclusion using a Query (2000)

    Thank you Hans, this worked great!

    Best,

    Scott

Posting Permissions

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