Hi,
Good question. Your scenario is definitely possible in Access 2013 web apps using data macros. I've done this many times myself for similar scenarios in my apps. There are probably a couple of different ways you could design the macro logic for this, but here is one way to accomplish your goal.
I created a small sample following your provided information to test out and the data macro logic works just fine in my test runs. I've added some comments to the macro logic so you can understand a little more what is going on and so you can use it as a learning tool.
For my sample I just created three fields in the two tables - ID, Field1, and Result ID. You will of course have to modify this logic for your sample since you have different/other fields to copy/append from the NewTemp table to the Master table.
Create a new named data macro (Home > Advanced > Data Macro) and then copy and paste all of this data macro logic I have here directly into your macro window:
<?xml version="1.0" encoding="UTF-16" standalone="no"?>
<DataMacros xmlns="http://schemas.microsoft.com/office/accessservices/2010/12/application"><DataMacro><Statements><Comment>Loop through each of the imported records in the NewTemp table</Comment><ForEachRecord><Data><Reference>NewTemp</Reference></Data><Statements><Comment>Create a local variable through each pass and set it to No to assume that each record is unique coming in</Comment><Action Name="SetLocalVar"><Argument Name="Name">varIDExists</Argument><ExpressionArgument Name="Value"><Expression><Original>"No"</Original><StringLiteral Value="No"/></Expression></ExpressionArgument></Action><Comment>Now attempt to lookup a record in the Master table that has the same Result ID value</Comment><LookUpRecord><Data><Reference>Master</Reference><WhereCondition><Expression><Original>[Master].[Result ID]=[NewTemp].[Result ID]</Original><FunctionCall Name="="><Identifier Name="Master.Result ID" Index="0"/><Identifier Name="NewTemp.Result ID" Index="1"/></FunctionCall></Expression></WhereCondition></Data><Statements><Comment>If we reached here inside the lookup record that means we found a match in the Master table for the Result ID field. This means the record already exists so change the local varilable value to Yes to for the next step</Comment><Action Name="SetLocalVar"><Argument Name="Name">varIDExists</Argument><ExpressionArgument Name="Value"><Expression><Original>"Yes"</Original><StringLiteral Value="Yes"/></Expression></ExpressionArgument></Action></Statements></LookUpRecord><Comment>Now that we are finished with the lookup record action, check the value of the local variable. If the variable is still No, that means no record was found in the Master table and the varilable wasn't changed. This means we are safe to create the new record in the Master table by copying each value in the NewTemp value for the specific record we are processing right now.</Comment><ConditionalBlock><If><Condition><Expression><Original>[varIDExists]="No"</Original><FunctionCall Name="="><Identifier Name="varIDExists" Index="0"/><StringLiteral Value="No" Index="1"/></FunctionCall></Expression></Condition><Statements><Comment>Create the record now in the Master table</Comment><CreateRecord><Data><Reference>Master</Reference></Data><Statements><Action Name="SetField"><Argument Name="Field">[Master].[Field1]</Argument><ExpressionArgument Name="Value"><Expression><Original>[NewTemp].[Field1]</Original><Identifier Name="NewTemp.Field1"/></Expression></ExpressionArgument></Action><Action Name="SetField"><Argument Name="Field">[Master].[Result ID]</Argument><ExpressionArgument Name="Value"><Expression><Original>[NewTemp].[Result ID]</Original><Identifier Name="NewTemp.Result ID"/></Expression></ExpressionArgument></Action></Statements></CreateRecord></Statements></If></ConditionalBlock></Statements></ForEachRecord><Comment>Now that we finished checking this record in the NewTemp table, move to the next record in the NewTable table. The macro will reset the varilable again at the start of the next loop.</Comment></Statements></DataMacro></DataMacros>
You should be able to see everything I did in your macro window. Before you try to save this data macro, you'll need to adjust of course for the Field1 reference I have in my sample and add in all the other extra field names you need to copy on separate SetField actions in the appropriate spot.
Now save that named data macro (perhaps name it AppendRecords) and then attempt to run it from the browser window. I assume you already know how to do this part - define a UI macro on a view that uses RunDataMacro and select your saved named data macro.
If you've done it correctly, Access should skip over the existing records and only add new ones to the Master table.
I should point out that as the number of records in your NewTemp table increases, this will take longer and longer to process. If possible, you might want to try experimenting with restricting the number of records for Access to loop through by applying some kind of Where clause in the outermost ForEachRecord action. That will speed up the time to append the new data.
Hope this helps.
I've also attached a screenshot of the macro window in my test example for your learning as well as anyone else reading this.
--------------------
Jeff Conrad - Access Junkie - MVP Alumnus
Senior Content Developer - Modern Assistance and Support Experience - Microsoft Corporation
Author -
Microsoft Access 2013 Inside Out
Author -
Microsoft Access 2010 Inside Out
Co-author -
Microsoft Office Access 2007 Inside Out
Access 2007/2010/2013 Info:
http://www.AccessJunkie.com
----------
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/en-us/legal/Copyright/Default.aspx
----------