Query for latest record based on date field

[number1pita]

Hello,

The database (non Access) we have didn't gain historical perspective that we could report from, so we are data dumping into an Access database. I am trying to build a query where it will update a field in a table (field "Exempt" in tblSeptemberRR) based off the most recent entry in another table (field "Risk Exempt" based of field "Last Updated" date in tblEngagement).

Would someone be able to help me in figuring out how to do this? I tried to create a select table where it would include all the unique identifiers, the field Risk Exempt from tblEngagement and then tried Max of Last Updated, but when I view it, it shows both dates for the unique identifier, not the most recent.

Any help would be greatly appreciated.

Thanks!

 

[Micron]

Try this, but first back up the table (save a copy) to be affected.
Create a query "qryREforMaxDate" that gets the Risk Exempt value based on the maximum date, assuming that is what you mean by most recent entry. Dump this sql into the query sql view window, run it, and confirm it is the correct value by looking at your table:

SELECT [tblEngagement].[Risk Exempt] FROM [tblEngagement] 
WHERE ((([tblEngagement].[Last Updated])=(SELECT Max([tblEngagement].[Last Updated]) AS 
MaxOfDate_Submitted FROM [tblEngagement])));

Then create a query in the same manner to update the target table:

UPDATE [tblSeptemberRR], [qryREforMaxDate] 
SET [tblSeptemberRR].[Exempt] = [qryREforMaxDate].[Risk Exempt];

If you choose to view this in datasheet view before actually running it, it should show you how many rows will be updated. Note, you may see values, or empty rows depending on whether or not anything is already there. What you are looking at is not the impending result - it is just the number of records that will be affected.
Hopefully, I have kept your table/field names straight in my head! If it does not work and you cannot figure out why, be sure to post what you created and any error numbers with error message text. Something as simple as a missing space between two words can cause this to fail.