Database Latest Duplicate Query filter

I built a linked database for work that pulls data from another database to track inventory assets. The original database that I am pulling from compiles data of serial numbers from work performed on vehicles every 6 months (so vehicles will eventually be entered multiple times in its table). The new database that I am using to extract this data is now showing the duplicate vehicles as well. I need this new database to only show the most recent date for each vehicle in its query (no duplicate vehicles, so we are only seeing where all of the equipment is currently).

In my example photo of the query you can see Bus# 11 duplicated. I only want the most recent PM Date. I have never used the SQL so any help you can provide would be greatly appreciated.

Linked-Query.png
Here is my SQL code curently:

SELECT PMFixed.[Bus#], Max(PMFixed.PMDate) AS MaxOfPMDate, PMFixed.[FBSN#], PMFixed.[BTSN#], PMFixed.[BVSN#], PMFixed.[CVSN#], PMFixed.[TRSN#], PMFixed.[LBSN#], PMFixed.[IVUSN#], PMFixed.[RadioSN#]

FROM PMFixed

GROUP BY PMFixed.[Bus#], PMFixed.[FBSN#], PMFixed.[BTSN#], PMFixed.[BVSN#], PMFixed.[CVSN#], PMFixed.[TRSN#], PMFixed.[LBSN#], PMFixed.[IVUSN#], PMFixed.[RadioSN#];

Open in new window


If I remove the serial numbers from the query I get the proper filter, but I need the serial numbers displayed with only the most recent bus# entry for each bus:

workingSQLresult.png
Michael RoseberryAsked:
Who is Participating?
 
PortletPaulfreelancerCommented:
Use a subquery (aka "derived table") of the Bus number and the maximum date to choose which rows you want from the source table.
select
        PMFixed.*
from PMFixed
inner join (
       select PMFixed.[Bus#], Max(PMFixed.PMDate) AS MaxOfPMDate
       from PMFixed
       group by PMFixed.[Bus#]
       ) m on PMFixed.[Bus#] = m.[Bus#] and PMFixed.PMDate = m.MaxOfPMDate

Open in new window

1
 
PatHartmanCommented:
You need a correlated subquery that finds the max date for each Bus# and uses Bus# and date to retrieve the details of that record.

PS it is poor practice to use embedded spaces and special characters in your object names.  I don't see any function or property names but you need to avoid them also.  Stick with upper and lower case letters, numbers, and the underscore if you want a separator character because you don't like CamelCase.
0
 
Michael RoseberryAuthor Commented:
Thank you for your reply! Yes I regretted using the symbols but that was what was requested by others that don't understand the software...
I have tried using the Max on bus as well but it still shows the older date in the list. I need to see only the newest date with all of the serial numbers included. I just can't get my head around how to properly code it. I have seen codes for other people that it worked for them but I couldn't get it to work on mine (Slightly different scenarios). I really appreciate your assistance if there is anything you can think of...
MaxBusMaxDateDesignView.pngMaxBusMaxDate.png
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
PatHartmanCommented:
Dare I repeat myself
You need a correlated subquery that finds the max date for each Bus# and uses Bus# and date to retrieve the details of that record.

If you post a working database, I'm sure that someone will build it for you.
0
 
Michael RoseberryAuthor Commented:
Forgive me for not being clear Pat. I understand that. I am not as familiar with correlated subquery so I was reaching out for help on this topic to solve my issue. This database is linked to another database and is extracting this data from it. I will post it anyways just in case it does not require the Linked info. Or perhaps i will rebuild it without the link and dump a copy of that data into it. But the links will have to be added into the coding again...
Sunset-Asset-Manager.accdb
0
 
Michael RoseberryAuthor Commented:
Use a subquery (aka "derived table") of the Bus number and the maximum date to choose which rows you want from the source table.
select
        PMFixed.*
from PMFixed
inner join (
       select PMFixed.[Bus#], Max(PMFixed.PMDate) AS MaxOfPMDate
       from PMFixed
       group by PMFixed.[Bus#]
       ) m on PMFixed.[Bus#] = m.[Bus#] and PMFixed.PMDate = m.MaxOfPMDate

PortletPaul, Thank you soooooo much! Your solution worked perfectly!!!! You have no idea how appreciative I am. I have been trying to figure out the inner join for days now.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.