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 RoseberrySenior Electronics TechnicianAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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 RoseberrySenior Electronics TechnicianAuthor 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
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
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

Michael RoseberrySenior Electronics TechnicianAuthor 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
PortletPaulEE Topic AdvisorCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Michael RoseberrySenior Electronics TechnicianAuthor 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.