Get latest entry in table for each car

[orange]

tagteam,

No I wasn't really taking a dig at you. It was more that the concept --"it's pretty close so that's good enough" --reminded me of a new hire many years ago. We were discussing a verification validation scheme and he came up with a similar ---"well it covers most things"... He has since become quite "tenacious" in his approach to data management and most of it stemmed from a little discussion about borrowing money at the bank. Once we discussed the situation in terms of his bank account; his hard earned money... things took a different turn. The importance of a complete test -- became very important.
Close is sufficient in those cases where you know certain conditions ---eg. only rolling stock to be accounted.



PS.
It sounds like there still may be something "wrong"/inefficient with your query(s).
But you said

the query does run quickly.

and I think that was the original issue to be addressed. So if it runs quickly, and handles all required "cars"....
Arvis' logic seems sound.

You might want to post a copy of the database and query(s) with only a sample of the data. We don't need 5,000,000+ records.

Good luck with your project.

Update: I see Arvi has posted a revision to the query join in Post #17.

[ArviLaanemets]

I have done as ArvilLaanemets suggested and the query does run quickly. However, the problem is that with the join it creates 1,097 copies of railcar 1 and 1,113 copies of railcar 2 etc... etc.... making the query huge. it should have about 4,200 records and ends up having 4,289,154 records.

It looks like I hadn't best day today. OK, let's try again.

Create a saved query

qLastDates = 
	SELECT RailCarID, MAX(DateOfImport) AS [LastDate]
	FROM FinalizedTraceData 
	GROUP BY RailCarID

Run a query

	SELECT ftd.DateOFImport, ftd.RailCarID, ftd.LocationCity, ftd.State 
	FROM FinalizedTraceData ftd 
		JOIN qLastDates ld ON ld.RailCarID = ftd.RailCarID AND ld.LastDate = ftd.DateOfImport

I left half of join condition out! Btw, I think in Access is used JOIN instead of INNER JOIN (I'm too used to SQL Server syntax).

[June7]

Access use INNER JOIN, LEFT JOIN, RIGHT JOIN, not just JOIN alone.

[ArviLaanemets]

Access use INNER JOIN, LEFT JOIN, RIGHT JOIN, not just JOIN alone.

OK. I was without Access at home computer, so there was not a way to check immediately. I remember some time ago I had issues when I used SQL syntax I'm used in SQL server in Access - probably it was then something like 'LEFT OUTER JOIN'.

[tagteam]

I am working on a sample data set, I should be able to post it today.