Update field from another table (1 Viewer)

lookforsmt

Registered User.
Local time
Today, 11:52
Joined
Dec 26, 2011
Messages
672
Hi!

I am trying to do basic update query. But have challenge when i run the 2 update query to update the same field from another table it overrides the existing data, so if the field not matching it removes the data and leaves it blank.

I am trying to explain as below:

I have three tables with the fields

tbl_Master:
RefAC
SOLId
Location

tbl_SOL:
SOLId
Location

tbl_VIP:
RefAC
SOLID
Location

1) Update Query:
Updates field "Location" in tbl_Master from tbl_SOL "Location" Join Properties "SOLId"
Result: Matching SOLID gets updated in tbl_Master.location

2) Update Query:
Updates field "Location" in tbl_Master from tbl_VIP "Location" Join Properties "RefAC"
Result: It overides the exiting location in tbl_Master and updates matching VIP.Location. (This is the issue)

New Result: Update tbl_Master.Location only where the field is matching and leave the existing data as is.

Any help in update query
 

MarkK

bit cruncher
Local time
Today, 00:52
Joined
Mar 17, 2004
Messages
8,178
I don't see why you need three tables for that data. I would put all that in one table, then I would never need to move stuff around, I could just edit it in-situ.
hth
Mark
 

lookforsmt

Registered User.
Local time
Today, 11:52
Joined
Dec 26, 2011
Messages
672
Thanks for the reply MarkK

tbl_Master has field "Location" empty which will be updated from tbl_SOL
The updated "Location" is standard locations. I have to update the VIP locations from tbl_VIP, therefore does this through query update twice.

Is there a better way?
 

MarkK

bit cruncher
Local time
Today, 00:52
Joined
Mar 17, 2004
Messages
8,178
What is the broader purpose? I would expect VIP and SOL to be data in a field, not the name of a table. What is a "Master?" Maybe say more about what your system does, what it is for, and we can understand better how to make a model of that problem using tables.
hth
Mark
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 03:52
Joined
Feb 19, 2002
Messages
42,981
It overides the exiting location in tbl_Master and updates matching VIP.Location. (This is the issue)
That is what it is supposed to do.

I'm confused by your explanation but it may be that you need to add criteria on thie second update query. I just don't know what it needs to be because I'm not sure what you are trying to do.

Please try to explain what data is where and what condition you want to apply or not apply the update from the second query.
 

lookforsmt

Registered User.
Local time
Today, 11:52
Joined
Dec 26, 2011
Messages
672
Apologies for the incomplete info.

tbl_Master maintains data of customers where there docs will be dispatched.
SOLID is the default branch code where the customer belongs to. Unfortunately the data does not publish the location in the tbl_Master. So have to run the update query between tbl_Master and tbl_SOL (Example1)

tbl_SOL is the fixed branch codes with area locations where the customer originally belongs and the docs to be delivered.
Eexample1. 1203 belongs to Area A; 1205 belongs to Area B; etc.

tbl_VIP has original SOLID where they belong with new Locations of customers. Common between the two tables is field "RefAC"
Then run the 2nd update query between tbl_Master and tbl_VIP to get the new location in the same field."Location"

The issue here is it matches the RefAC and inputs the NewLocation but it leaves blank the other customers Locations which should not be changed.

tbl_VIP is the special arrangements where the docs need to be delivered.
Example2. customer belongs to 1203 but he may want to gets his docs delivered to area 1205 or customer may belong to 1300 and wants his docs to be delivered to another locations 1345, etc.

I hope i have been able to provide enough information.:banghead:
Thank you all for looking into this.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:52
Joined
May 7, 2009
Messages
19,169
You may merge the two or three tables and add Office address, delivery address, mailing address, etc.
 

lookforsmt

Registered User.
Local time
Today, 11:52
Joined
Dec 26, 2011
Messages
672
Thanks Arnelgp,

I managed to get the result. I was using the join properties 2nd option, which was overriding the data. But when i selected the 1st option it is working fine and it only updates the where the data is equal.

Thanks and apologies for spending you all precious time.
 

Users who are viewing this thread

Top Bottom