Thanks, I'm working on it now.
Quick question then, how would I set up SQL queries without use of a predefined composite key? My import data lacks a primary key that is synced with the Main Table's, meaning I would have to update it based on the pre-existing fields already in the data.
For example:
tblAccessTable: (This is what the main table in access looks like)
PK| Composite | ContractNum | Years
1 | contract122017 | contract12 | 2017
2 | contract122017 | contract12 | 2018
tblImportIntoAccess: (I need to import this into the main table)
Composite | ContractNum | Years
contract122017 | contract12 | 2017
contract122018 | contract12 | 2018
contract132014 | contract13 | 2014
My SQL query would look like this:
Code:
UPDATE tblAccessTable As Main
RIGHT JOIN tblImportIntoAccess As Import ON Main.Composite = Import.Composite
SET
Main.Composite = Import.Composite,
Main.ContractNum = Import.ContractNum,
Main.Years = Import.Years;
Without the Composite field, how would I update the main table with the import table? In the article you linked it suggested that there could be a 'unique' index that one could use to ensure uniqueness, then should i keep the composite field around and use it as a index instead so that in the main table, the primary key is what is used for access records, however the 'unique' index i.e. composite should be used for updating purposes? How do people usually deal with this?