If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Query to extract data help please
I have one field in a table that contains 5 different values, each seperated
by a ",". (Example: Value1,Val2,V3,Value4,Valu5). Each value may be of different lenghts (6 characters, 4 characters, etc.) The only common factor is that each if seperated by a comma (,). Would anyone know how to run a query to extract the first value and put in into a new table in field1, then put the next value in field 2, etc. In orther words to take the entire value of this field and put in into seperate fields. Thanks in advance for any suggestions. T |
#2
|
|||
|
|||
Query to extract data help please
Today is your lucky day! Since the data is seperated with commas, Access can
handle it with a little work. First of all does that table have a primary key? If so great. If not create an autonumber field in the table and make it the primary key. Next create a query with the primary key field and the field in question. However you need to join these two fields together with a comma. Something like this: SELECT [CommaPK] & "," & [TheCommasField] AS CSVed FROM tblCommas; Run the query and make sure that it looks OK. After running it, export the query results as a text file. When you get to the Export Text Wizard dialog box about Text Qualifier select None. After exporting the query to a text file, import it back in using Get External Data. This will create a new table which you can join to the existing table using the primary key field. The above is something good for a one-time cleanup of data. If this is something that you have to do frequently, there may be other ways. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Terry DeJournett" wrote: I have one field in a table that contains 5 different values, each seperated by a ",". (Example: Value1,Val2,V3,Value4,Valu5). Each value may be of different lenghts (6 characters, 4 characters, etc.) The only common factor is that each if seperated by a comma (,). Would anyone know how to run a query to extract the first value and put in into a new table in field1, then put the next value in field 2, etc. In orther words to take the entire value of this field and put in into seperate fields. Thanks in advance for any suggestions. T |
Thread Tools | |
Display Modes | |
|
|