Appending the most recent record

[SalesOp]

Hi all

In an append query with employee records that may have different information, I would like to only append to a new table the most recent record when multiple records exit for the same employee. With a primary Key in Employee Id how can I do this?

The employees location, title, manager, etc may have changed since the last quarter and I want to keep the latest changes.

Appreciate any help,

Sales

 

[plog]

This will require a subquery to determine 'the most recent record'. The first step in that is defining what 'the most recent record' means. What field determines this?

Whatever it actually is, let's call it [OrderField]. Also, call the source table you are appending from--tblFrom, and the table the data is being appended to--tblTo. Your subquery then becomes this:

SELECT EmployeeID, MAX([OrderField]) AS MostRecentOrderField
FROM tblFrom

Name that 'sub1'. Ite essentially finds the most recent value of [OrderField] and identifes the record's data you want in tblFrom. Next, make a new query based on sub1 and tblFrom. JOIN them like so:

tblFrom.EmployeeID = sub1.EmployeeId
tblFrom.OrderField = sub1.MostRecentOrderField

Bring in all the fields you want to append to tblTo and make that query an APPEND query. That will bring in the 'most recent record' (however you define that) to tblTo.

 

[SalesOp]

Dear Plog,

Thanks for the response. I tried the but I must be doing something wrong. Kindly look at the attached DB. At the end I should only have the most recent records ordered date wise. The old records should not be copied.

Thanks

 

[plog]

sub1 is incorrect. It only has 1 field in it. Reread my instructions for building it.

Also, why do you have an action query. Those are usually a sign that you are doing something wrong.

 

[SalesOp]

Hi Plog,

When I create the sub1 subqyery exactly as you suggested:

SELECT tblEmployeeData.EmployeeID, MAX([MonthId]) AS MostRecentOrderedField
FROM tblEmployeeData;

However when I try to create an append query with sub1, I get an error "Your query does not include the specified expression "EmployeeID"as part of the aggregate function."

As I am not well versant with SQL script I am creating it using the Access query create interface.

I need to create a Totals (Aggregate Query) to make sure I select one (the latest) among the records for a given employeeid.

I keep getting different error messages. Sorry about this but I am not sure how to move further

 

[plog]

Sorry, I missed a clause:

SELECT tblEmployeeData.EmployeeID, MAX([MonthId]) AS MostRecentOrderedField 
FROM tblEmployeeData;
GROUP BY tblEmployeeData.EmployeeID

Use that for sub1.

Now, I still don't know why you are using an APPEND query. Those are a sign of doing something wrong. Also, when you have 2 tables with the eact same structure, you are doing it wrong as well. I would convert your APPEND query to a SELECT query and just use that when you need that data. There's no need to 'move' data or make copies of things.

 

[SalesOp]

Thanks plog, worked like a charm.