Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2008
    Posts
    150

    Unanswered: Suggestiont to Eliminate around 60 Rows from a Query

    Hello,

    I need a suggestion on the correct way to remove a Work Order number from my query.

    I am running a report to pull various data, but I have to eliminate around 60 work order numbers from the query. I am only able to add around 35 numbers to the criteria grid, the rest will be truncated.

    Should I upload the work order numbers into a table and then somehow add that table to my query? If so, how would I set it up? OR is there a better way to do what I need?

    Appreciate your help on this one.

    Thank you.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    So how do you 'know' which rows to include (or exclude)

    Its certainly obe way of handling ad hoc querying. Use a temporaey table, write the data there then join to that table. That join can be used to limit to rows that match or conversely rows that dont match

  3. #3
    Join Date
    Sep 2008
    Posts
    150
    Hi Healdem,

    Thank you for responding. I have a table called "WO_Eliminate" with just 1 column of work order numbers that I do not want to see in my query results.

    How would I join the table to my main table?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so you need to use a left join to that table, with that table being on the RHS of the join

    eg:-

    Code:
    select my, column, list from mytable
    LEFT JOIN WO_Eliminate on mytable.acolumn = WO_Eliminate.othercolumn
    WHERE isnull(WO_Eliminate.othercolumn)
    the LEFT JOJN means return all rows from the LHS of the join (the mytable in the example above) AND all rows that have a match in WO_Eliminate. if thgere is no match in WO_Eliminate then return NULL in place. SO by adding 'WHERE isnull(WO_Eliminate.othercolumn)' you then eliminate any rows in the resultset where there is a NULL value in WHERE isnull(WO_Eliminate.othercolumn). meaning you only get rows whose workorder number is not in WO_Eliminate.

    essentially you can do the same task by having numbers you want included by entering then into a tabel and the rejecting rows which are not NULL
    Code:
    WHERE NOT isnull(WO_Eliminate.othercolumn)
    If you don't use SQL directly then you can get the same effect by opening your query in the query designer and then select the JOIN (the link between the two tables) and define the link as required. I forget the weasel words Access uses but you should be able to suss it out. Then add a column from WO_Eliminate table and in the criteria set it to isnull. I rarely use the query designer myself, preferring to use SQL directly (yes I am one of the cro magnon developers who hates GUI interfaces which tend to look real purdy and come between you and what you want to achieve )
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    You could also use
    Code:
      WHERE mytable.acolumn not In (SELECT othercolumn FROM WO_Eliminate)
    ??

    Not sure if this any more efficient than an outer join though?


    MTB

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by MikeTheBike View Post
    Hi

    You could also use
    Code:
      WHERE mytable.acolumn not In (SELECT othercolumn FROM WO_Eliminate)
    ??

    Not sure if this any more efficient than an outer join though?


    MTB
    One way to find out would be for the original poster to run both options on as big a dataset as possible, see what happens, then report back
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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