Fill in Column

[srpatel]

Hello all,

I need to update my table which has about 2000 records in it. I need to update a column depending on what Location is in the record.

Below is my table structure:

tblLoc

LocID
LineID
LCCode
LCDesc
LocType
AssetLocTypeID

tblLine

LineID
Line

tblALLBS

BS_ID
Asset_Num
LineID
Location
AssetLocType


**Location = has the same info as LCCode.

The criteria is that every Location that has ODD numbers as the last two digits should = S

every Location that has Even numbers as the last two digits should = T

And at the beginning of the LCCode if there is BD or DD = D

Examples of code:

D166
D165
D015
DD102

All codes are four digits long and one which are for D are 5 digits long. The column I need to fill in is the AssetLocType in the ALLBS table.
I would appreciate some adivce on how i should build this sql statement or does a function need to be used?

Thanks

SRP
Have an Awesome Day!

 

[mp9]

Do you want to just update tblALLBS.AssetLocType then? And if Location has the same info as LCCode, can't you use that to determine how to update AssetLocType? So something like (UNTESTED):

UPDATE tblALLBS SET AssetLocType='S'
WHERE CByte(Right(Location,1)) IN (1,3,5,7,9);

UPDATE tblALLBS SET AssetLocType='T'
WHERE CByte(Right(Location,1)) IN (0,2,4,6,8);

UPDATE tblALLBS SET AssetLocType='D'
WHERE Left(Location,2) IN ('BD','DD');

http://www22.brinkster.com/accessory/

 

[rjoubert]

You can create three separate queries to handle this.

Query 1:

UPDATE tblALLBS SET tblALLBS.AssetLocType = "S"
WHERE (((CInt(Right([tblALLBS]![Location],1)) Mod 2)=1) AND ((Len([tblALLBS]![Location]))=4));

Query 2:

UPDATE tblALLBS SET tblALLBS.AssetLocType = "T"
WHERE (((CInt(Right([tblALLBS]![Location],1)) Mod 2)=0) AND ((Len([tblALLBS]![Location]))=4));

Query 3:

UPDATE tblALLBS SET tblALLBS.AssetLocType = "D"
WHERE (((Len([tblALLBS]![Location]))=5));

Good Luck!

 

[srpatel]

Hello,

Thank you for your help with queries, rjoubert and mp9. I will test these out and see i get on.

Thanks
SRP
Have an Awesome day!

 

[PHV]

Why THREE queries ?
UPDATE tblALLBS
SET AssetLocType=IIf(Mid([Location],2,1)='D','D',IIf(Val(Right([Location],1)) Mod 2,'S','T'))
WHERE Len([AssetLocType] & "")>=4

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886