Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2015
    Posts
    4

    Unanswered: Excluded results in query

    It is kind of hard to explain.
    This is my start

    Click image for larger version. 

Name:	City result.png 
Views:	8 
Size:	13.5 KB 
ID:	16114

    I need to keep all of these fields but if you notice there is a number that is the same in both Trailer Number In and Trailer Number Out. I need to exclude any records WHERE the value exists in both fields from the query results
    and also include Bobtail Out if it is True

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Please post the SQL statement of the query.
    Have a nice day!

  3. #3
    Join Date
    Jan 2015
    Posts
    4

    SQL Statement

    SELECT [Trailer Pool Date for Edit].[Company Name], [Trailer Pool Date for Edit].City, [Trailer Pool Date for Edit].State, [Trailer Pool Date for Edit].[Date In], [Trailer Pool Date for Edit].[Trailer Number In],
    [Trailer Pool Date for Edit].[Trailer Number Out], [Trailer Pool Date for Edit].[Bobtail Out]
    FROM [Trailer Pool Date for Edit]
    WHERE
    ((([Trailer Pool Date for Edit].City)="Clyde")) AND [Trailer Pool Date for Edit].[Trailer Number In]<>[Trailer Pool Date for Edit].[Trailer Number Out]
    OR
    ((([Trailer Pool Date for Edit].City)="clyde") AND (([Trailer Pool Date for Edit].[Bobtail Out])=True));

    Here is the current SQL Statement but is not working.

    [Trailer Pool Date for Edit].[Trailer Number In]<>[Trailer Pool Date for Edit].[Trailer Number Out] does not provide the desired result

    The above picture is of the results of this SQL Statement

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Not sure to understand (or afraid of understanding). In the sample data you posted, there is no row with Trailer Number In matching Trailer Number Out:
    Code:
    Company Name	City	State	Date In	        Trailer Number In		Trailer Number Out	Bobtail Out
    WHIRLPOOL FDC	CLYDE	OH	1/15/2014	693726	                <>	654873	      	        0
    WHIRLPOOL FDC	CLYDE	OH	1/15/2015	688123	                <>	693179	      	        0
    WHIRLPOOL FDC	CLYDE	OH	1/16/2015	5147397	                <>	694422	      	        0
    WHIRLPOOL FDC	CLYDE	OH	1/16/2015	690686	                <>	5147397	      	        0
    Do you mean that you want to exclude any row if it has a matching value in any column of any other row (e.g. [Trailer Number In] of row 3 matches [Trailer Number Out] of row 4)?
    Have a nice day!

  5. #5
    Join Date
    Jan 2015
    Posts
    4

    Not going to work

    You hit the nail on the head as to what I wanted but I have been thinking about this and it is not a working solution. What if a trailer comes in to a facility more than once. I don't want it excluding the record if it hasn't left yet so I have to come up with another solution to this problem. Thanks for helping me. I have another idea I don't want to switch up topics in this thread if you don't mind looking at my question before re-post in another thread just message me and I will run it by you or at least you might be able to point me to the right thread.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you also record the time an event occurred (or even the time the row was updated then you coudl exclude rows based on the time
    or you coudl order by dtae + time.....
    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
  •