Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Need Help to create "YARD IN QUERY"

  1. #1
    New Member
    Join Date
    Feb 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Need Help to create "YARD IN QUERY"

    The goal for this query is to achieve the yard report at any time I run the query. To clarify when I run this query I want to know: what the trailers status loaded/empty/other currently in the yard. I don't need any information on the trailers that are out of the yard. The status must change if the trailer leaves the yard: in other words it will not appear on the query anymore.


    These are four columns IN/OUT, LOADED/EMPTY, TRAILER#, DATE & TIME


    IN/OUT LOADED/EMPTY TRAILER DATE & TIME
    IN LOADED 456667 2/10/2019 1:12:00 PM
    IN EMPTY 53004 2/11/2019 2:00:00 PM
    IN LOADED 53005 2/10/2019 2:35:00 PM
    OUT LOADED 53005 2/10/2019 1:00:00 PM
    IN EMPTY 53006 2/11/2019 10:35:00 AM
    IN LOADED 53665 2/9/2019 4:00:00 PM
    OUT LOADED 54334 2/10/2019 3:10:00 PM


    Please advise how I can get those results in the query. "HELP ME"

  2. #2
    BatCoder
    Join Date
    Feb 2002
    Location
    Turkey
    Posts
    862
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need Help to create "YARD IN QUERY"

    Welcome to the MrExcel Board!

    If you are trying to get the latest status of each trailer, then you need an inner join that will return the most recent date value (maximum value of the date field) for each trailer, and retrieve the matching date records for the same trailer.

    The sample query (used your headings as field names, and Table1 as the table name):

    Code:
    SELECT t.trailer, t.loadedempty
    FROM Table1 AS t 
    INNER JOIN (
          SELECT trailer, max(datetime) AS lastDate 
          FROM Table1 GROUP BY trailer
       )  AS tt 
       ON t.trailer = tt.trailer AND t.datetime = tt.lastDate;
    Last edited by smozgur; Feb 16th, 2019 at 08:30 PM.
    Suat

  3. #3
    New Member
    Join Date
    Feb 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need Help to create "YARD IN QUERY"

    Thanks for reply but when I run this query that ask for "Date time" and "T Date time" and after entering 1st date and last date I don't get any entry.

    But if you can set when I run this query then I don't need to enter any date.

  4. #4
    BatCoder
    Join Date
    Feb 2002
    Location
    Turkey
    Posts
    862
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need Help to create "YARD IN QUERY"

    It is supposed to work without problems.

    Could you please tell me your exact field names in the table?
    Suat

  5. #5
    New Member
    Join Date
    Feb 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need Help to create "YARD IN QUERY"

    I tried again with some changes and it work but I am getting trailer out status as well but I need query if any trailer went out then I don't need that particular trailer number in the list. Only trailers in the yard that I need in the query.

  6. #6
    New Member
    Join Date
    Feb 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need Help to create "YARD IN QUERY"

    My field name is TRL (FOR TRAILER), LES (LOADED EMPTY STATUS), IOS (FOR IN OUT STATUS), DT (FOR DATE TIME)

  7. #7
    New Member
    Join Date
    Feb 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need Help to create "YARD IN QUERY"

    And my table name is "ACTIVITY"

  8. #8
    BatCoder
    Join Date
    Feb 2002
    Location
    Turkey
    Posts
    862
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need Help to create "YARD IN QUERY"

    Then you need to exclude inout = "OUT" records in your query:

    Code:
    SELECT t.trailer, t.loadedempty
    FROM Table1 AS t 
    INNER JOIN (
          SELECT trailer, max(datetime) AS lastDate 
          FROM Table1 GROUP BY trailer
       )  AS tt 
       ON t.trailer = tt.trailer AND t.datetime = tt.lastDate
    WHERE t.inout <> "OUT";
    Suat

  9. #9
    New Member
    Join Date
    Feb 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need Help to create "YARD IN QUERY"

    Wooooooooooooooooooooooooooooooooow it works. You are great

  10. #10
    BatCoder
    Join Date
    Feb 2002
    Location
    Turkey
    Posts
    862
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need Help to create "YARD IN QUERY"

    SQL is great

    Glad to hear it helps!
    Suat

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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