move values in ID

[onressy]

hi I have a tabel with 15 columns, 5 of which i'm concerned with. SID, ID1, ID2, ID3, ID4

Because of the new application all records must have an SID. Sometimes the SID values are empty sometimes null and some have a numeric from 0-80

How would i handle a query that for every SID=""
update with the ID1 value from the same record; but ID1 itself is ="" ; Then check ID2 and so on for ID1 to ID4, so if a value is found then update SID with it, as well update itself with nothing. the order of updating SID range from ID1 then ID2 thru to ID4.

Thnx
Any suggestions on how to hadle this?

 

[Remou]

This may be a start:

UPDATE Table10 SET Table10.SID = Left(LTrim([ID1] & " " & [ID2] & " " & [ID3] & " " & [ID4] & " "),InStr(LTrim([ID1] & " " & [ID2] & " " & [ID3] & " " & [ID4] & " ")," ")), Table10.ID1 = IIf([ID1]=Left(LTrim([ID1] & " " & [ID2] & " " & [ID3] & " " & [ID4] & " "),InStr(LTrim([ID1] & " " & [ID2] & " " & [ID3] & " " & [ID4] & " ")," ")),Null,[ID1]), Table10.ID2 = IIf([ID2]=Left(LTrim([ID1] & " " & [ID2] & " " & [ID3] & " " & [ID4] & " "),InStr(LTrim([ID1] & " " & [ID2] & " " & [ID3] & " " & [ID4] & " ")," ")),Null,[ID2]), Table10.ID3 = IIf([ID3]=Left(LTrim([ID1] & " " & [ID2] & " " & [ID3] & " " & [ID4] & " "),InStr(LTrim([ID1] & " " & [ID2] & " " & [ID3] & " " & [ID4] & " ")," ")),Null,[ID3]), Table10.ID4 = IIf([ID4]=Left(LTrim([ID1] & " " & [ID2] & " " & [ID3] & " " & [ID4] & " "),InStr(LTrim([ID1] & " " & [ID2] & " " & [ID3] & " " & [ID4] & " ")," ")),Null,[ID4])
WHERE (((Table10.SID) Is Null));