Update qry based on other line

[robmason10]

Have asked something similar earlier but am lost again so will start again..

I have 2 tables - tbl Merged was used to create tbltrans but I now need to update some ID fields in tblTrans (as these were auto creates they didn't exist before so couldnt do it...) anyway..

tblMerged
F1 LinkedID

TblTrans
TransID linkedID

What I basically need to do is update TblTrans.LinkedID with the value of TblTrans.TransID for the transaction where LinkedID = the value of F1

Does that make any sense at all? - i.e....

TblMerged
F1 LinkedID
1 Null
2 3
3 5
4 Null
5 Null

TblTrans
TransID LinkedID
a Null
b 3
c 5
d Null
e Null

TblTrans - Desired
TransID LinkedID
a Null
b c
c e
d Null
e Null

Thanks in advance

 

[ESquared]

I take it you're getting the c and e because those records are in positions 3 and 5? Is TransID the main sort field? I could write a query to do this, but I have reservations about what you're trying to do. Are you 100% sure that TransID, sorted ascending, will have the right value in the right position?

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

 

[robmason10]

Not quite - in fact I made an error on my example.

Basically tblMerged is DTS to create tblTrans - when tblTrans is created then tblTrans.TransID is created - I then need to link tblTrans and tblMerged together (using other linked fields to get the matched rows) I then need to UPDATE tblTrans.LinkedID with the tblTrans.TransID WHERE linkedID = F1 - they wll not be sorted/in correct position

 

[ESquared]

Give an example, please.

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)