Splitting a comma seperated value into multiple columns using SQL View

Splitting a comma seperated value into multiple columns using SQL View
I have a table that looks something like this:

Column1 Column2 Column3
John 12, 34, 5,498 Texas
Mike 16, 8, 69, 54 New York
Robert 104, 42, 110 Mexico
Josh 25 Maine

I am trying to seperate the values in Column2 into multiple columns that hold single values so that for instance the first row's Column2 holds each value in a different Column:
Column1 Column2 Column3 Column4
12 34 5 498

Basically I am trying to normalize the data and without doing this, I cannot move forward. I would really appreciate if someone can help me solve this using some sort of SQL Query in Access's SQL View.

Sponsored Links:

Related Topics

Splitting a name field into two columns
I have to split a filed that has Lname, Fname into two columns of Lname and Fname. I know how to do this in Excel (using left, right by calculating the len using the comma) but I don't know the functions or the code to do this in Access.


Reselect Listbox entries from text string read
I have a gathering of items selected in a multi-select Listbox saved to a table as a comma seperated string. Can I use that string to re-select the listbox items, let's say, if the end user was re-opening to modify previous selections?

I have code to parse and read from comma to comma - what I need is how to reselect the list items


Multiple Values seperated by commas
I am nearly done my database and it has been a long and grueling affair, the one last feature I was hponig to add would be to have a multiple keyword search.

Each entry in the table has a description matched to it so I thought it would be a good feature on the search form that words separated by commas would be searched as a opposed to one word at a time. I realize this could be done with multiple search boxes, which is how I have now but it would have a cleaner look to condense into one box.

The current SQL I have now is:

((Drawings.Description) Like "*" & [Forms]![search].[search] & "*" And (Drawings.Description) Like "*" & [Forms]![search].[search3] & "*"));

is there a way to implement a comma separated format in one search bar


Query Criteria based on a text box of multiple comma seperated values
I need to run a query to return data on multiple customer numbers. I have a form with a text box. The query's criteria is based on the text.
If I manually input the criteria in the query as Customer_Num in(100,200,300) it works.200,300 in the text box of the form, the query (Customer_numin([Forms]![frm_Main]![txtCustomer#])) it returns no rows.


Splitting positive and negative figures into seperate columns
hoping someone can help

I have a table called Table1 and within it are various columns including a Transactions column that contain both credit and debit (positive and negative) transactions. As well as the Transactions column in Table1 there is also an Account Number column. There are several account numbers and each account number has several transactions.

The end goal is to create a query that splits the debit/credit transactions into separate columns and then totalled for each account.

Iv tried creating one query with <0 criteria under the Transactions field for negative and vice versa for positive then a third to bring the two columns together but its not really working!

Ideally it would be great to do from one query but think it may be unlikely to do from the query design view.

Whilst I have no experience with SQL, I am confident of following any clear guidance offered


One Table Split to Multiple Tables
I have an old database that I had to redesign. Unfortunately, it had multiple, unlinked tables with duplicate fields in each of those old tables. They basically made it a database with diffeent reports and forms to enter and manipulate their data with.

I have created another database, splitting all of those tables and relating them correctly. Because of the size of some of those tables, I created One to One as well as One to Many tables and have them normalized through 5th.

Now comes the hard part.

I need help with porting the data, one table at a time, from the hold database into the multiple tables of the new database. How can I do this? Everything I have read about Insert and Append queries is for multiple tables into one table. how can I do the reverse of that?

I don't care if I use VBA, a macro or SQL, I just need to use something. Both of these databases are Access 2007 on an XP machine. Afterwards I will be splitting backend from frontend and inserting new data from an Excel file


Separating VALUES in FIELDS/COLUMNS
I have a table with two columns, one column is Address and the second column is City. However, the values under those fields are combined with Address and City, separated by a comma. How do I delete everything before the comma in the City field and delete everything after the comma (including the comma) in the Address Field

currently it looks like this:

Address City<-----Fields
1280 One St, San Jose 1280 One St,San Jose, CA<----Values


Splitting One Column Into Multiple Columns
I have an Excel workbook, where a language column has 1 to 5 languages. For example:
Language
Record 1: English
Chinese
Spanish

The 3 languages are separated by hitting the enter key, and not the space bar key. I have about 400 records of this issue.

Is there a way I can split the language column into 5 columns at most? And then, if a record only has 3 languages, only the first 3 columns are populated?

FYI, I have MS Access


SQL Code to Make Query / Report
I have created a form that will generate a SQL statement based on many user choices regarding columns to be in the query/report, and filters. I have tested my code and it generates a valid SQL statement that I can enter into a query SQL View.

I am trying to use this SQL statement to either alter a pre-made query so that it can be exported to Excel, or create a temporary query so that it can be exported to Excel.

I would prefer to do query instead of report since there are 40 columns that can be chosen. I have been doing some research and have not found a close match.


SQL Code to Make Query / Report
I have an interesting challenge right now.

I have created a form that will generate a SQL statement based on many user choices regarding columns to be in the query/report, and filters. I have tested my code and it generates a valid SQL statement that I can enter into a query SQL View. I am trying to use this SQL statement to either alter a pre-made query so that it can be exported to Excel, or create a temporary query so that it can be exported to Excel. I would prefer to do query instead of report since there are 40 columns that can be chosen.

Has anyone seen this done before? I have been doing some research and have not found a close match


getting a comma separated string from a recordset in MS Access
I have an access query that has 2 columns: CAUSE and count(records) having the respective CAUSE value. I want to add another column that will give me all the ACCOUNT NUMBERS same as the number mentioned in the COUNT column. Also I want all these ACCOUNT NUMBERS to appear in comma separated format on the crystal report.

Any suggestion as to how we can convert the recordset to a comma separated string


VBA Parse Tab Delimited Text File
Data supplied in tab delimited text file. Unfortunately there are more than 255 columns in the text file and can't directly import into an MS Access table (max 255 columns). Now trying to parse the text file (using VBA) to remove unwanted columns - idea being that once below 255 columns I can load into table.

I'm okay with parsing on tab character, chr(9), but I've hit problem if data record contains comma character. VBA INPUT verb appears to consider comma to be a variable delimiter.


"New query in design view" defaulting to "all fields" (2000)
When creating a new query in design view no matter which fields I drop into the design grid, when you preview the results ALL the table fields appear. Even if I don't select ANY fields in the design grid (which should cause an error on preview), the preview shows all fields.

If I switch to SQL view I can plainly see that darn asterisk in the SELECT statement.

Is this a bug that there is a fix for? If I use the wizard this does not happen. And of course I can create my query then switch to SQL view and remove the last comma and asterisk from the select statement.


Multiple column select
I have transferred a whole lot of data from spss to access.tables have 400+ columns of data. I get requests to select data out of this, sometimes 200-300 columns a time. Is there a way of inserting a list into the select statement, example SELECT (filename) FROM table;

In this case, filename is a comma separated list of variables in the table specified in the FROM part


Missing Columns in Datasheet View
I was working on my database and opened one of my tables in datasheet view and noticed that I was missing some columns. I switced to design view and all of the fields are there. So, why doesn't datasheet view show all of my columns (fields)? Is there a setting somewhere that hides them


field with musltiple entires
I have a column in a table where in one cell, I have multiple entries like: 2, 23, 45, 88 - separated by comma.

I was wondering what I may do in teh quesry, so that I get rid of the entries after teh first comma in the cell


Removing multiple items from multiselect listbox
Hope someone can help.

I have a listbox on a form with letters waiting to be printed. I need to be able to remove multiple items at once using an SQL statement.

The main problem is that I need to include multiple columns in the SQL as I loop through the items.

What I need is something like lst_print.itemdata(i).column(0) but this doesn't work.

I've tried changing the bound column but this de-selects the remaining items.


Crosstab query for multiple columns hierarchy
I have list of Employees who earn points for ProjA and ProjB for every month.
Using Pivot Table View I can see Employees with columns divided by Months
and this column further sub divided by ProjA and ProjB.
I cannot use this pivot table on the report form, so I tried creating a Crosstab query

However, I can't specify to show columns first grouped by Month and then by Proj A and B

If I choose only for ProjA, the sql is:
TRANSFORM First(Emp.ProjA) AS FirstOfProjA
SELECT Emp.ID
FROM Emp
GROUP BY Emp.ID
PIVOT Emp.Month;

The attached screenshot shows the Pivot Table View at the top and the CrossTab Query at the bottom. The crosstab query should be fixed to look like the table at the top
The months are designated by numbers

How can I show columns grouped by Month and then show for both Projects A&b


[ACCESS 97] Search multiple keywords
I have searched the forum but I just haven't found quite what I am looking for.

I have a form with one unbound texbox (txtZoekenOpTrefwoordCursusCode).

My search function as shown below works great with 1 keyword.

If Me.txtZoekenOpTrefwoordCursusCode = "" Or IsNull(Me.txtZoekenOpTrefwoordCursusCode) Or Me.txtZoekenOpTrefwoordCursusCode = 0 Then
If sFilter = "" Then
sFilter = ""
Else
sFilter = sFilter & ""
End If
Else
If sFilter = "" Then
sFilter = "[CursusCode] Like '*" & Me.txtZoekenOpTrefwoordCursusCode.Value & "*'"
Else
sFilter = sFilter & " and [CursusCode] Like '*" & Me.txtZoekenOpTrefwoordCursusCode.Value & "*'"
End If
End If

The problem is that I want to search on multiple keywordt seperated by a comma


Storing multiple dates in a single field!!
I am facing an issue with an access table. I have a field named "Contact Dates" , supposedly that field will store in a drop down list all the dates that the contact with the related company was made, how can I store it ? I am using in the current moment a memo data type to store the data and a comma for splitting them.