Topics Search

Splitting a comma seperated value into multiple columns using SQL View

Splitting a comma seperated value into multiple columns using SQL View
Views: 161
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:

More topics

 

Splitting a name field into two columns

Preview
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

Preview
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

Preview
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

Preview
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

Preview
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

Preview
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

Preview
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

Preview
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

Preview
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

Preview
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