kay soo
asked on
Update Query For Unique Manual Stamped ID For Non Index Duplicates in Two Tables
Hi, I am not quite sure how to come about the query for the intended results, am not sure if that is possible, hopefully Gurus here can help out.
Both Table share the same structures, LinkedTbl and LocalTbl
LinkedTbl LocalTbl
StampedId Client StampedID Client
31 Client1 31 Client1
32 Client2 32 Clinet2
33 Client3 Client3
34 Client3 Client3
35 Client3 Client3
I tried to create a query to update the blank StampedId field from LinkedTbl to LocalTbl so that I able to get the following end results, but it was updated with the last number (35) from LinkedTbl to the null StampID field in LocalTbl, perhaps due to duplicate Clients in Client Column (must allowed duplicate Client entry in column), there are no other field in both Tbls allowed for index.
Supposed results after update query:
LinkedTbl LocalTbl
StampedId Client StampedID Client
31 Client1 31 Client1
32 Client2 32 Clinet2
33 Client3 33 Client3
34 Client3 34 Client3
35 Client3 35 Client3
My query syntex as follow, not quite right...
UPDATE LocalTbl, LinkedTbl SET LocalTbl.StampedId = linkedTbl.StampedId
WHERE (((LocalTbl.StampedId)=[li
Appreciate any advice, thx
Both Table share the same structures, LinkedTbl and LocalTbl
LinkedTbl LocalTbl
StampedId Client StampedID Client
31 Client1 31 Client1
32 Client2 32 Clinet2
33 Client3 Client3
34 Client3 Client3
35 Client3 Client3
I tried to create a query to update the blank StampedId field from LinkedTbl to LocalTbl so that I able to get the following end results, but it was updated with the last number (35) from LinkedTbl to the null StampID field in LocalTbl, perhaps due to duplicate Clients in Client Column (must allowed duplicate Client entry in column), there are no other field in both Tbls allowed for index.
Supposed results after update query:
LinkedTbl LocalTbl
StampedId Client StampedID Client
31 Client1 31 Client1
32 Client2 32 Clinet2
33 Client3 33 Client3
34 Client3 34 Client3
35 Client3 35 Client3
My query syntex as follow, not quite right...
UPDATE LocalTbl, LinkedTbl SET LocalTbl.StampedId = linkedTbl.StampedId
WHERE (((LocalTbl.StampedId)=[li
Appreciate any advice, thx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Ryan Chong
by the way, what about no of records? will no of records in both tables suppose to be identical as well?
ASKER
Yes, both tables eventually shared the same records.
I am trying to sync same stamped ID for records in both tables, I hv created another update query for LocalTbl linked by StampedID, that is why the update of Stamped ID from LinkedTbl to LocalTbl is important before I run the second update query.
Given a choice I try not to delete LocalTbl and append from LinkedTbl due to heavy network traffic.
I am trying to sync same stamped ID for records in both tables, I hv created another update query for LocalTbl linked by StampedID, that is why the update of Stamped ID from LinkedTbl to LocalTbl is important before I run the second update query.
Given a choice I try not to delete LocalTbl and append from LinkedTbl due to heavy network traffic.
Given a choice I try not to delete LocalTbl and append from LinkedTbl due to heavy network traffic.
is that possible to fix the root cause by appending the records to table: LocalTbl with non-null StampedID, rather than update the records in LocalTbl while there is no unique identifier to update such records?
ASKER
Just cross my mind, is there a way to run a sequential records update reference query from LinkedTbl to LocalTbl?
is there a way to run a sequential records update reference query from LinkedTbl to LocalTbl?how do you determine the absolute sequence if there is no unique identifier?
ASKER
True, I guest hv to go for delete and append the null records accordingly, thx for sharing