Create and Update a Single Table from SQL Backend

Question

Good Morning All,

I'm having problem pulling the data I need from a SQL Backend and keeping it up to date.

I've got two tables, that hold the data at need. At one Point they were split due to a software update we received. First Table dbo_PT_NC Second Table dbo_PT_Task

Primary key of PT_NC is the "NCR" Field, The Task Table has its own Unique ID, But the PT_Task.TaskTypeID field is linked to the "NCR" field

SELECT dbo_PT_Task.TaskTypeID, 
dbo_PT_NC.NCR, 
dbo_PT_NC.NCR_Date, 
dbo_PT_NC.NC_type, 
dbo_PT_NC.Customer, 
dbo_PT_NC.Material, 
dbo_PT_NC.Rev, 
dbo_PT_NC.Qty_rejected, 
dbo_PT_Task.TaskType, 
dbo_PT_Task.Notes AS dbo_PT_Task_Notes, 
dbo_PT_NC.Origin, 
dbo_PT_NC.Origin_ref, 
dbo_PT_NC.Origin_cause, 
dbo_PT_NC.Origin_category
FROM dbo_PT_NC INNER JOIN dbo_PT_Task ON dbo_PT_NC.[NCR] = dbo_PT_Task.[TaskTypeID]
WHERE (((dbo_PT_NC.NCR_Date)>=#1/1/2016#) AND ((dbo_PT_Task.TaskSubType)="Origination"))
ORDER BY dbo_PT_NC.NCR_Date, dbo_PT_NC.Customer;

After I have this data pulled and put into a Snapshot (I do not want the Live Data to be accessible by the front end users) I'll be adding columns for a Weak Point Management System we are implementing, Fields Such as:

Scrap Code (lookup field to another table i've built inside excel)
Containment, Root Cause, Plan, Do, Check, and Act, all of which Should most likely be Memo Fields (As characters may break 255)
Date Completed A date the process was complete

This table (The data i've snapshotted and the new fields added) will need to be updated with New or Changed Records from the SQL Backend i've previously connected to.

UPDATE Big thanks to Andre.. Got it working, Sample code below (i've added more update fields since)

UPDATE tblWeakPointMaster, dbo_PT_NC INNER JOIN dbo_PT_Task ON dbo_PT_NC.NCR = dbo_PT_Task.TaskTypeID 
SET tblWeakPointMaster.Qty_rejected = [dbo_PT_NC].[Qty_rejected], 
tblWeakPointMaster.dbo_PT_Task_Notes = [dbo_PT_Task].[Notes], 
tblWeakPointMaster.Material = [dbo_PT_NC].[Material], 
tblWeakPointMaster.Rev = [dbo_PT_NC].[Rev], 
tblWeakPointMaster.NC_type = [dbo_PT_NC].[NC_type]
WHERE (((tblWeakPointMaster.NCR)=dbo_PT_NC.NCR) And ((tblWeakPointMaster.TaskID)=dbo_PT_Task.TaskID));

Answer 1

I assume there is a 1:n relation between PT_NC and PT_Task?

Then you should include both primary keys in the import SELECT.

Either use them as composite primary key in the Access tables instead of the new KEY column. Or if that is impractical because other tables are linking to tblWeakPointMaster, you can also keep that primary key.

But in any case, these two columns form the JOIN between tblWeakPointMaster and tblWeakPointUpdates.

All other columns can be used to update tblWeakPointMaster from tblWeakPointUpdates (assuming they can be edited in the original database).

Edit: if you don't use them as composite primary key, you need to create an unique index on the combination, or the JOIN will not be updateable, I think.