Access SQL query to update field

[celestedeeva]

Hello all,

I have a field with the following inside each row stored as text.

ID Value
1 21 34 5
2 21 5
3 21 34

I need to do two things 1st I need to update the field "Value" to:

ID Value
1 21,34,5
2 21,5
3 21,34

2nd I need to create the table to represent:

ID Value
1 21
1 34
1 5
2 21
2 5
3 21
3 34

Any help would be greatly appreciated.

C.

 

[Remou]

You can use Replace to do the first thing:

[tt]Replace([Value]," ",",")[/tt]

You can use a Recordset and Split to do the second thing. Here are a few notes:

astrVals=Split(rs![Value])
For i=0 To Ubound(astrVals)
   strSQL="Insert Into tblTable (ID, Value) Values (" _
   & rs!ID & ",'" & astrVals(i) & "'"
   Currentdb.Execute strSQL
Next

Value is a reserved word, so you may have probelms if Value is the name of your field.

 

[celestedeeva]

Thank you very much however I am getting a syntax error for the first part her is what I put in in the SQL area of my query:

Select sample.balance
Replace ([Balance]," ",",")
From sample;

 

[Remou]

If that is a cut and paste of the SQL, you are missing a comma:

Select sample.balance,
Replace ([Balance]," ",",")
From sample;

You will also need to check for nulls:

Replace(Nz([balance],"")," ",",")

 

[JoeAtWork]

# 2 would be very easy to do if you normalized your tables. Problem is with #1 you are storing multi-values in a field, which is a big no-no in relational databases.

 

[celestedeeva]

Thanks this worked - I was my intention was to put the commas into the field so that I can delimit upon import - is there an alternative way to import so the field would be delimited based upon spaces between values so:

ID Balance
1 2 3 4
2 1 2 4

would be imported as:

ID Balance1 Balance2 Balance3
1 2 3 4
2 1 2 4

from there I would then want to create:

ID FinalBalance
1 2
1 3
1 4
2 1
2 2
2 3

Thanks so much for this information it is so helpful - I am filling in for a co-worker and not familiar with SQL or Access.

Celeste