Parsing the table field

[Abg]

I am new user. Is it possible to parse one of the field in the table and move the parsed data into other two field of the same table?
For example, I have data PKY87987aa-012334 in the first field. PKY87987aa will go into second field of the same table and 012334 will go to the third field of the same table.
Is it possible? If yes, how can we do it?
Thanks

 

[pbaldy]

You can use an update query, and depending on the consistency of the data, the appropriate string function(s): Left(), Right(), Mid(), InStr(), etc. More info in Help.

 

[Abg]

Thanks Paul.
The format of tha data in the first field is consistent.
The format is PKYxxxxaa-yyyyyyyy , where x,y= numbers.

--Ash

 

[pbaldy]

That makes it easier. Check out the functions and post back if you get stuck.

 

[SOS]

Just to mention something that wasn't mentioned -

I hope that if you are doing this in the table that you are NOT keeping the first column once you have done this. If you are planning on keeping that column, then you shouldn't be adding these other two columns and instead use a query to have them available.

 

[Abg]

Hi Paul,
I have designed an update query as you suggested but getting an error of non updating all record. I attached a pdf file of Query grid

 

[vbaInet]

Hi Paul,
I have designed an update query as you suggested but getting an error of non updating all record. I attached a pdf file of Query grid

Go to the sql view, and paste the sql string here

 

[Abg]

Here is the SQL:

UPDATE SNEntry SET SNEntry.ScanSerialNumber = "ScanSerialNumber", SNEntry.Tanapa = Left([ScanSerialNumber],9), SNEntry.[Serial Number] = Right([ScanSerialNumber],9);

 

[pbaldy]

I don't think you want to update the original column.

 

[Abg]

Paul,
It worked fine after I removed original column from the Query grid. Thank you for help.

Ash

 

[pbaldy]

No problem, and welcome to the site by the way!

 

[The_Doc_Man]

Now, what you did seems to work for you, but may I offer an observation and suggestion that is probably safer?

Never do parsing in-place on a table you intended to keep. Make a temporary table or two or six (however many you need), then run your query on the temps until you have it right. Then run the final version of your query, delete the original table, rename the temp as a replacement for the original table. In the process, if you broke apart one long field into two or more fields, the final table should not include a copy of the original table - because it already does. You can use a query with string concatenation to make a "view" of what that original field looks like. But rather obviously, it was important for you to have the field split into its pieces-parts.

Here's a rule of thumb. The final table should be in the format that you need most often. Use queries to "convert" it into other formats when you need them. That includes re-merging fields that sometimes need to be together.

 

[Abg]

It is good idea. Thank you.

 

[chundusmgs]

I am new user. Is it possible to parse one of the field in the table and move the parsed data into other two field of the same table?
For example, I have data PKY87987aa-012334 in the first field. PKY87987aa will go into second field of the same table and 012334 will go to the third field of the same table.
Is it possible? If yes, how can we do it?
Thanks

you can use update query with mid function.for further details contact chundusmgs@rediffmail.com