Need Help to create "YARD IN QUERY"

dhawan_aj

New Member
Joined
Feb 16, 2019
Messages
8
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"
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
1,006
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:

dhawan_aj

New Member
Joined
Feb 16, 2019
Messages
8
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

BatCoder
Joined
Feb 28, 2002
Messages
1,006
It is supposed to work without problems.

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

dhawan_aj

New Member
Joined
Feb 16, 2019
Messages
8

ADVERTISEMENT

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

New Member
Joined
Feb 16, 2019
Messages
8
My field name is TRL (FOR TRAILER), LES (LOADED EMPTY STATUS), IOS (FOR IN OUT STATUS), DT (FOR DATE TIME)
 

dhawan_aj

New Member
Joined
Feb 16, 2019
Messages
8

ADVERTISEMENT

And my table name is "ACTIVITY"
 

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
1,006
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";
 

Watch MrExcel Video

Forum statistics

Threads
1,108,818
Messages
5,525,077
Members
409,618
Latest member
gkllc

This Week's Hot Topics

Top