Log updates and new records, duplicate records how to select the most recent

[sk84gtspd]

Hey y'all,

I have a question and I'm having trouble putting into words what I'm trying to do so I couldn't really search the posts. If this has already been asked and solved please just direct me there. Thanks.

There may be a better way of doing this, but I have a Log table where I make an insertion for every new or updated record in the Main table. The Log table keeps track of all of the changes to the main table.

The Log table has all the same fields as the main table except I added a LOG_PK to make sure there are not any exact duplicates.

I want to write a query that will mimic the Main table but only contain records that have been updated or newly created.

So I want every record with a MAIN_PK but if there are duplicate MAIN_PKs I want the record with the greatest LOG_PK.

If you will look at the screen capture of the Log Table. I want to write a query that will have a resulting table where the highlighted rows and field is removed.

Basically I'm going to use this updates table to make an XML to update the table in a different database. Generating the desired table doesn't have to be the only solution. In fact I think it might be impossible so I'm interested in any solution that will help me get a table of only records that are new or updated.

Thanks

 

[CJ_London]

you'll need to use a subquery - something like

SELECT *
FROM logTable
WHERE ID=(SELECT MAX(ID) FROM logTable as T WHERE main_PK=logtable.main_PK)

 

[sk84gtspd]

Thanks CJ_London that was exactly what I needed. I had switched tasks during my internship and when I came back to Access I was sort of struggling to get up and going.