Updating Records in a Table Based on Records in Another Table?

[firebirdta84]

I have an Access table named MasterCases that contains 5 fields - CaseNumber, CaseName, User, Event and EventDate. Every day, I receive another Access table called UpdatedCases from an outside source that contains these same five fields - but with updated information in the Event and EventDate fields. The UpdatedCases table does not contain all the records that are in MasterCases, only ones where the Event and EventDate have changed or ones with new CaseNumbers. My question is this - how can I make Access compare the CaseNumber and User fields of these tables and if they match, have the Event and EventDate from the UpdatedCases table overwrite the Event and EventDate fields for that CaseNumber in MasterCases? Would this just be an Update Query? It would also need to append any new records from UpdatedCases where the CaseNumber and User combo isn't found in the MasterCases table. Thanks in advance!

Joe

 

[Remou]

You need two queries, first the update query using an Inner Join on CaseNumber and User fields and then an append query joined on the same fields, but using a Left Join and appending only those records with Nulls for CaseNumber and User fields.

 

[PHV]

Have a look here:
thread701-1053292

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[firebirdta84]

Okay, I got it to update the records where the two fields match...still trying to figure out how to append the records from UpdatedCases that aren't in the MasterCases.

Joe

 

[Remou]

Please post the sql you have for updating.