Need Help to create "YARD IN QUERY"

[dhawan_aj]

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"

 

[smozgur]

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):

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;

 

[dhawan_aj]

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.

 

[smozgur]

It is supposed to work without problems.

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

 

[dhawan_aj]

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.

 

[dhawan_aj]

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

 

[dhawan_aj]

And my table name is "ACTIVITY"

 

[smozgur]

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

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";

 

[dhawan_aj]

Wooooooooooooooooooooooooooooooooow it works. You are great

 

[smozgur]

SQL is great

Glad to hear it helps!