Hello from Toronto (1 Viewer)

I need you help in Microsoft Access to fix one query.


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

I need only those entries that have "IN" and if under "Trailer" section have 2 same number then only those
come in the result whoever have greater "Date & time" and if "Out" is greater than I don't need that entry.

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

First, in EXCEL you can have headers with special characters, but in Access they are not a good idea. Particularly that ampersand, but the slashes are not good either. To keep from having typing cramps, you might wish to clean up those names if you are going to have to work on this a lot.

Second, you posted a technical question in an introductions sub-forum. One of the moderators will probably move this, and seeing as how you are new, you didn't know our ways. So... take this as friendly advice. If you have a technical question to post, post it in the specific sub-forum. You will get faster attention that way. In your case, since it is a queries question you could have posted in the queries sub-forum. But like I said, you are new here and we make allowances. So that is NOT a slam against you, just advice.

I want to clarify your question for myself or for anyone else who tackles this. Are you saying this:

1. If you have IN and OUT with same TRAILER then if OUT time is greater, include neither entry. I.e. skip both entries.

2. If you have IN and a later IN for the same TRAILER, include only the later IN.

3. If you have an OUT with no matching IN for the same TRAILER, skip that entry, too.

4. (Not addressed in your words) Can it ever happen that you would have an OUT with a later IN on the same day with the same TRAILER number? I.e. the opposite of what I called case 1?

5. (Not addressed in your words) Does the "IN with a later IN" rule or the "IN with a later OUT" rule (either one) apply if the later entry is on a different day?

Thanks for reply and advise, actually I new in Microsoft Access and will fix as you advise.

1. as you wrote is good.
2. as you wrote as good as well but if their is IN mostly their will be OUT as well in order to bring another IN. It is yard database if IN entry happen and in order to bring another IN entry their will be OUT entry in between.
3. as you wrote is good.
4. yes that is also happen, like if one trailer firstly came IN and after is OUT and in the end that back again on the same day so in that case last entry I need to show in my list.
5. If today we enter IN and tomorrow or day after OUT then I don't need that entry in the list.

Mostly I need IN entries if any trailer is OUT I don't need that entry in my list.

I hope that clear all your answers but let me know if not clear

The part that is a bit confusing and thus is unclear to me - you talk about trailer entry and exit from your truck yard, and I understand that part. My grandson sometimes works as a security guard at a truck yard and deals with truck arrivals and departures and has talked about it a little. Therefore, I understand truck yard movement. But what I don't understand now has to do with dates.

What I (we) need is to know IN ENGLISH what it is you are trying to find or create with this query. We have sample data (so you don't need to post any more data) but we don't know the focus of the query. Are you looking for a daily status report? Because you talk about dates but then talk about whether it was in and out on different days.

So is this a daily yard inventory at a certain time or is this trailer-oriented or something else. I guess the question is... OK, let's say you get the query that you need. What is the intended presentation? Like maybe it answers the question "What trailers were in the yard at midnight of a given date?" Or is it "What trailers are in the yard at any time that I run the query?" Do you see what I mean about the language-oriented description of the goal?

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.

Got it - "Current yard status."

I'm going to use short field names for this only because of typing issues.

In/Out status will be IOS. Loaded/Empty status will be LES. Date & Time will be DT. Trailer will be TRL. The data table will be called YARD.

I'm going to do this using two queries.

QMaxDT:

QYardStatus:

The idea is that the QMaxDT query computes the latest status entry for each trailer, whatever it is. The QYardStatus only looks at the records matching that last status entry and, if it is not IN, you don't see it.

What you want might at least resemble this. If it is not perfect, then it should be close.

Let me test both and let you know today and tomorrow.

I am getting below error.

THE MICROSOFT ACCESS DATABASE ENGINE CANNOT FIND THE INPUT TABLE OR QUERY 'QMaxDT'. MAKE SURE IT EXISTS AND THAT ITS NAME IS SPELLED CORRECTLY.

Please advise how I can fix.