Limit query to only show the latest record

[marcustofeles]

Hi all, crossforum double-post here in desperation...



I'm trying to put together a small database for tracking and tracing a fleet of shipping containers. One function I need is to query which containers that are currently at one specific location.
The query below helps me to see what units are at a certain location, based on the input of the location name in a form field. However, if the same container have visited the location more than once then it shows up twice (or more).

How can I limit the query to not show container duplicates? Also, I want the most recent dated location-visit to show, but I'm guessing that the ORDER BY-criteria take care of that.

SELECT Tracking.Container, Tracking.Location, Tracking.Arrivaldate, Tracking.Loaded, Tracking.Empty
FROM Tracking
WHERE (((Tracking.Location)=[Forms]![Containerpositioning]![location]))
ORDER BY Tracking.Arrivaldate DESC;

Regards
Marcus

[orange]

Marcus,
Can you show your tables and relationships --- Tools-->Relationships as a jpg?
It would help us understand your db.
It is unclear what field represents

most recent dated location-visit

.
You will probably need a subquery along this line

..... Where ShipDate = ( Select Max(ShipDate) from Tracking .....)

[marcustofeles]

Hi Orange,

Here you go.
I dont't have any relationships at all...

Attachment 4660


Much obliged

[orange]

If you only have 1 table, and the PK is the ID of the container, then you can only have 1 record per container.

Please describe your situation with sample data.

[marcustofeles]

The primary key is not the container, it's a default ID given by the system (in laymen terms...)

Can I upload the database here?

[orange]

According to the graphic you sent, the ID is the PK, but I guess it is just a record id.
I believe you can upload your mdb here.

[marcustofeles]

Here is the database. My apologies for it being in swedish, hopefully you'll be able to understand anyway!

Here is also a screenshot of what field in the form that, filled in, is a condition for the query.



All your help is greatly appreciated.
Br Marcus

[orange]

Marcus,
I'm not sure what I'm looking for????
You have a filter on the data on your form

[container]='NOBU 111111-1'

[orange]

Marcus,
Adjust your query to this

SELECT Tracking.Container, Tracking.Position, Tracking.Ankomstdatum, Tracking.Lastad, Tracking.Tom
FROM Tracking
WHERE
Tracking.Ankomstdatum = (Select max(Ankomstdatum) from Tracking
where (((Tracking.Position)=[Forms]![Containerpositionering]![position])))
)
;

This will only show the record with the latest Ankomstdatum for this
Position =[Forms]![Containerpositionering]![position]

[marcustofeles]

Hello Mr Orange!

I'm afraid I can't find the filter that you mentioned...

Your query did indeed return only the latest unit for a certain position, but it only showed one container, there should be more units at the position I queried.

Perhaps it's not possible to solve this with the setup I have?


I'm unbelievably grateful for your input.
Cheers
Marcus

[marcustofeles]

Hey man,

Seems to be solved.

SELECT Tracking.Container, Tracking.Position, Tracking.Lastad, Tracking.Tom, Tracking.Ankomstdatum
FROM Tracking INNER JOIN POS ON (Tracking.Container = POS.Container) AND (Tracking.Ankomstdatum = POS.Ankomstdatum)
WHERE (((Tracking.Position)=[Forms]![Containerpositionering]![position]));

Again, thank you for your efforts!

[orange]

Glad you got it under control.
Does it really give you the most recent dated location-visit.

I must admit I did/do not really understand that expression.
My thought was that if you had multiple Ankomstdatum then you only wanted the latest.I don't see how your solution does that, but as I said, I don't really understand the terms.
Glad you got it working.

[marcustofeles]

So far it works...

Cheers mate