how to update the content of a field depending on the content of another field ?

[orange]

Here are 2 ways to get the newname.



1: Add a field to your existing table
My test table: I added a new text field called tnewname to the table and left it empty.

id	tname	tnewname
12345600_9101112131415	Sue_S
12345621_9101112131415	Bob_Q
12345678_9101112131415	Jack_L

Then created a Select query QCorr1

SELECT Tcorr1TEST.tname
, Tcorr1TEST.id
, Left([id],9) & [tname] AS XX
FROM Tcorr1TEST;

With this result

tname      id                             XX
Sue_S    12345600_9101112131415    12345600_Sue_S
Bob_Q    12345621_9101112131415    12345621_Bob_Q
Jack_L    12345678_9101112131415    12345678_Jack_L

Note: This is a select query to get info from your existing table. You could use this depending on your set up. Or, you could change this to an Update query and popuate the new field in your existing table.

2: Manipulate your existing table to create a new table with only the fields you need.
Using your skeleton SQL with my field naming to create TCORR2

SELECT TCORR1Test.ID
, TCORR1Test.TName 
, Left(Tcorr1Test.id,9) & TCORR1Test.TName as NewName
INTO TCORR2
FROM TCORR1Test;

Results in

TCORR2

ID	TName	NewName
12345678_9101112131415	Jack_L	12345678_Jack_L
12345621_9101112131415	Bob_Q	12345621_Bob_Q
12345600_9101112131415	Sue_S	12345600_Sue_S

[nicoboss]

Here are 2 ways to get the newname.

1: Add a field to your existing table
My test table: I added a new text field called tnewname to the table and left it empty.

id	tname	tnewname
12345600_9101112131415	Sue_S
12345621_9101112131415	Bob_Q
12345678_9101112131415	Jack_L

Then created a Select query QCorr1

SELECT Tcorr1TEST.tname
, Tcorr1TEST.id
, Left([id],9) & [tname] AS XX
FROM Tcorr1TEST;

Hi

First I created a 3rd field in the table named "Name2"
Here is my code :

SELECT CORR1.Name, CORR1.id
, Left([id],9) & [tname] AS Name2
FROM CORR1;

When I paste it in SQL Direct and run it, an access window pops up asking me to select the input data in windows explorer.
I think this is nnot normal, so I guess I do it wrong ?

[nicoboss]

ah ok, it was not in SQL Direct, sorry