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.
Thank you for taking the time to report an issue.
What's wrong... Please write below.
I have code to parse and read from comma to comma - what I need is how to reselect the list items
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
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
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
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.
currently it looks like this:
1280 One St, San Jose 1280 One St,San Jose, CA<----Values
Record 1: English
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
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
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.