0

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!!!

2
Contributors
1
Reply
2
Views
7 Years
Discussion Span
Last Post by chuckc
0

This might or might not be the best way, but have you considered using Visual Basic to do this update? You can execute sql commands in VB for each of the smaller steps in your update process.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.