I am using MS ACCESS, and have 4 tables to update, from 2 different tables/spreadsheet
Where table
tb1- Client Index, Name, address and zip.
tb2- user index, Sales rep, client index
tb3- user index, Managers
tb4- client index, Sales Target
Master 1 has Name, address, Sales target and sales rep
Master 2 has Name, manager, zip
So, I need UPDATE the records as per:
From master 1 to tb1 with the client index (auto increment), name, details.
From master 1 to tb2, salesrep, tb1.clientindex,
where tb1.name=master2.name if not found, create a new record
update tb2 set clientindex=tb1.clientindex,
update tb2 set salesrep=master1.salesrep
From master 1 to tb4, target, tb1.clientindex,
where tb1.name=master2.name if not found, create a new record
update tb4 set clientindex=tb1.clientindex,
update tb4 set target=master1.target
From master 2 to tb1, target, tb1.clientindex,
where tb1.name=master2.name if not found, create a new record
From master 2 to tb3, manager,
where tb1.name=master2.name
and
update tb3
set index=tb1.index
set manager=master2.manager
The idea is the above, but.. Can anyone help me with this? (translating into a MSACCESS/SQL entry)
I am sorry.. it is a bit complicated, and it seems to be a bit too much for a noob!!!