Return record with date closest to today based off unique identifier field

[number1pita]

Hello,

I am capturing changes from a non-Access database by pulling daily extracts of anything that changes using the last updated field as my trigger to know if something changed. Everyday I get a report for any records updated yesterday and my Access database knows to update the tables adding the new records. It doesn't replace the old, because we want to keep that historical perspective. My only problem is, I need to build a query where it will pull all the current data and leaving out the old records. So I am trying to figure out how to create a query that will pull in all the fields, but only return the most recent "lastupdated" field based off the unique identifier field of "engagementid".

Any help on this would be greatly appreciated, because I am at a complete loss.

Thanks!

 

[Micron]

from a non-Access database

You mean changes in an Access database, because you're working in an Access database? You will probably need a query with a sub query (a sql statement as criteria) where the sub returns the Max([lastupdated]). You can research sub queries if you've never created one; they do have a couple of unique requirements. Without a copy of your db or at least some data, I'm not able to even attempt if for you.

 

[number1pita]

Yes, in a sense, capturing changes in Access of data from another database.

Thank you so much! This helped! I did a little research on what a sub query was and was able to get it to work. I figured I needed to do max of lastupdated field, but was trying it with all the other fields in the query and it wasn't returning only the one instance of the unique identifier, but now this totally makes sense on why. Appreciate it!

 

[Micron]

Awesome! I they can be a life saver, but sometimes I still struggle to get the dang thing working right off the bat.