Get latest entry in table for each car

[tagteam]

I have a database that I import data into each day. There are 4k+ cars that I track each day and I want to run a query that will return the most recent entry for each car.

I have tried

SELECT FinalizedTraceData.DateofImport, FinalizedTraceData.Initial, FinalizedTraceData.Number, FinalizedTraceData.LocationCity, FinalizedTraceData.State 
FROM FinalizedTraceData WHERE FinalizedTraceData.DateofImport = (SELECT MAX([FinalizedTraceData.DateofImport]) FROM FinalizedTraceData as X WHERE X.Number = FinalizedTraceData.Number)

However, I get the following error



ODBC--call failed
Microsoft SQL Server native client 11.0[SQL Server] an aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.


I am trying to get the Date, Inital, Number, Location City, State for each car but only get the most recent record.

Any help is appreciated.

[isladogs]

You could use DMax instead

[keviny04]

You may use Max(), since all your data come from the same table. You use DMax() when you need to bring up some other table. Your query should be a summation query, and it should look like:

SELECT car, Max(cardate) as maxdate FROM table GROUP BY car ORDER BY car

[ArviLaanemets]

Try

SELECT 
    ftd.DateofImport, 
    ftd.Initial, 
    ftd.Number,
    ftd.LocationCity, 
    ftd.State 
FROM 
    FinalizedTraceData AS ftd 
WHERE 
    FinalizedTraceData.DateofImport = (SELECT TOP 1 ftd0.DateofImport FROM FinalizedTraceData as ftd0 WHERE ftd0.Number = ftd.Number ORDER BY ftd0.DateOfImport DESC)

[tagteam]

I tried ArviLaanemets code but get a parameter box asking for FinalizedTraceData.DateofImport. I like this method and would like to get it to work.

However, I have also decided to us an id record that I have for each car (better because some cars have the same number but different initials)

So I end up with

SELECT     ftd.DateofImport, 
    ftd.RailcarID,
    ftd.LocationCity, 
    ftd.State 
FROM 
    FinalizedTraceData AS ftd 
WHERE 
    FinalizedTraceData.DateofImport = (SELECT TOP 1 ftd0.DateofImport FROM FinalizedTraceData as ftd0 WHERE ftd0.Number = ftd.Number ORDER BY ftd0.DateOfImport DESC)

But still get the Parameter box asking for FinalizedTraceData.DateofImport. So I changed the FinalizedTraceData.DateofImport right after the where clause to ftd.DateofImport and it runs and runs and runs, and hasnt stopped yet, so I dont think that is what I am looking for.

[keviny04]

As I said earlier, give Max() a try. Your method of a subquery is cumbersome for your goal, and a simpler method that would also improve performance would be better.

Does this work?

SELECT     
    Max(ftd.DateofImport) as maxdate, 
    ftd.RailcarID,
    ftd.LocationCity, 
    ftd.State 
FROM 
    FinalizedTraceData AS ftd 
GROUP BY
    ftd.RailcarID

[tagteam]

When I try that I still get the same parameter box asking for FinalizedTraceData.DateofImport. and if I run it with the parameter box empty it just runs and runs and i stop it after about a minute because even if that eventually worked it would be too slow for my purposes.

[June7]

Seems Access doesn't recognize the field name DateofImport. Perhaps:

SELECT ftd.DateofImport,
ftd.RailcarID,
ftd.LocationCity,
ftd.State
FROM
FinalizedTraceData AS ftd
WHERE
ftd.DateofImport = (SELECT TOP 1 ftd0.DateofImport FROM FinalizedTraceData as ftd0 WHERE ftd0.Number = ftd.Number ORDER BY ftd0.DateOfImport DESC)


Number has special meaning in Access. Probably should not use it as name for anything.

[orange]

How about posting a sample of your database with only a few hundred records?

You have to be aware of Access reserved words. You can prefix your field names to avoid reserved words.

Date and Number jump out. Suggest myDate, myNumber or similar.

[keviny04]

When I try that I still get the same parameter box asking for FinalizedTraceData.DateofImport. and if I run it with the parameter box empty it just runs and runs and i stop it after about a minute because even if that eventually worked it would be too slow for my purposes.

A parameter box usually indicates that the query doesn't recognize the expression, in this case FinalizedTraceData.DateofImport. You may want to check to make sure it exists. Go to the "Design View" of the query and see if any names show up differently from the others.

[keviny04]

You have to be aware of Access reserved words. You can prefix your field names to avoid reserved words.

Date and Number jump out. Suggest myDate, myNumber or similar.

You also need to watch out for reserved words that you created yourself. If you created custom variable or a custom module with a function named MyNumber, it's better not to name anything else MyNumber. That's why many people use de facto naming conventions like tblMyTable, intMyInteger, etc.

[tagteam]

The names are all correct. I checked those as soon as I saw the Parameter box. I am also NOT using Number. That was a field that was in place before I came to work on this database and I created the RailcarID field to use.

I also tried switching out FinalizedTrace Data with ftd before and it doesn't ask for a parameter but it runs forever.

So I really tested

SELECT ftd.DateofImport, 
ftd.RailcarID,
ftd.LocationCity, 
ftd.State 
FROM 
FinalizedTraceData AS ftd 
WHERE 
ftd.DateofImport = (SELECT TOP 1 ftd0.DateofImport FROM FinalizedTraceData as ftd0 WHERE ftd0.RailcarID = ftd.RailcarID ORDER BY ftd0.DateOfImport DESC)

This is a very large table 5,741,085 records.

I think I will just stick with what I have and run the query based on date bc I only miss a few cars doing it that way and it runs much faster.

[orange]

Interesting comment

I only miss a few cars doing it that way and it runs much faster.

Would you feel the same if it was your bank account?

[ArviLaanemets]

Oops! A typo! FinalizedTraceData.DateOfImport > ftd.DateOfImport.

I see you did find the typo. But when the table is so big, then this isn't very good solution, as for every row in main query you have to run the condition query over whole table.

In this case create a saved query, p.e. qLastDates:

 SELECT RailCarID, MAX(DateOfImport) FROM

FinalizedTraceData GROUP BY RailCarID

And then your query will be

SELECT ftd.DateOFImport, ftd.RailCarID, ftd.LocationCity, ftd.State FROM

FinalizedTraceData ftd INNER JOIN qLastDates ld ON ld.RailCarID = ftd.RailCarID

You have 2 queries, but you win at speed, as the query qLastDates is run once. With big table, the win is considerable.

Something is strange. An additional code tag is added automatically inside of code tag I entered - after 'FROM' . I tried to delete it, to delete all code tags and entering them anew - and additional tags are added again and again

Some additional thoughts.
With a table containing so many rows, you may consider to use SQL Server as back-end database. SQL Server copes better with big tables, is faster, and SQL syntax it uses is more advanced/has more possibilities.

In SQL server, you can:
1. Use WITH clause in sql, like <;WITH xxx AS (A Subquery expression) SELECT ... FROM Table INNER JOIN xxx ...>
2. Create a view like I proposed for Access (vLastDates) and use it for INNER JOIN;
3. Index the view vLastDates;

1. and 2. are essentially same. 3. speeds the query up additionally.

[tagteam]

Ha Ha Orange, very funny. No, I probably wouldn't, and that is why I switched my career from an accountant to IT. the cars missed are cars that are not moving and i actually don't need a report for them.

I do have plans to move this data to SQL Server, but probably not until next year. This scenario may actually help me get the approval to start that project.

I am going to keep working on this since Orange was taking digs at me.

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.

Obviously, this is a join issue where it is including one result for each option found. Soooo, I will try to work up a sample of the data set today to see if that will help us figure this one.