Queries :: Splitting Text Field Into Columns
Feb 15, 2014
Is there a function or query that I can split a field into different columns?
For example, I have First Name, Last Name, Address.
But I want to split the address field into Address1, Address2, Address3, Address4 as the initial Address field has a lot of characters with commas e.g. 11 London Road, Liketown, Likeshire, London.
So, I want to be Address1: 11 London Road, Address2: Liketown, Address3: Likeshire, Address4: London.
I thought that I can export the field using a simple query and then re-import it with using the text field into columns option, but it is time consuming.
View Replies
ADVERTISEMENT
Nov 28, 2014
I have one field called FULL_ADDRESS. From that field I am going to populate two other fields. One called ADD_1 and the other, ADD_2.
In the FULL_ADDRESS field I have the following text :
"BOX 2747 ROCKY MOUNTAIN HOUSE AB T4T 1L7"
I want ADD_1 updated to:
"BOX 2747 ROCKY MOUNTAIN HOUSE AB"
and ADD_2 updated to:
"T4T 1L7"
While I understand how to do an update if I was using a space as a reference, i.e.
Trim(Left([FULL_ADDRESS],Instrrev([FULL_ADDRESS]," ")-1))
Trim(Mid([FULL_ADDRESS],Instrrev([A]," ")+1))
I can't figure out how to skip the first space from the right, and reference to the second occurring space, so that I get "T4T 1L7" to populate the ADD_2 field, and everything to the left of "T4T 1L7" placed in the ADD_1 field.
View 6 Replies
View Related
May 3, 2012
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.
View 3 Replies
View Related
Jun 4, 2013
I have a single field in a table called "Client Contact", where users enter a semicolon between the name, address, and city state & zip. My reason for this was so we could copy client info with a single copy and paste (like from an email). But, on the final report, it needs to have these three parts split up into different lines, or even different textboxes. I can't find a way to do that.
View 1 Replies
View Related
Jan 25, 2005
I have a table field which long ago was merged from several other fields. When the data was merged into the field it was delimited by "1." then "2." up to "5."
Example: MergedField = "1.Animal 2.Large 3.African 4.Grey 5.Long Nose"
I now want to split it appart in a query where "1.Animal" goes into expression1, "2.Large" goes into expression2, etc.
I need to base the text on where the one number begins and grab everything until the next number in the mergefield is detected.
Can someone show me the syntax for this.
Thanks!
View 1 Replies
View Related
Apr 4, 2007
I have a column in a table that contains the date and the time, i want to be able to split this column into 2 so i have one column for date and one for time
msgtime
03/07/2007 09:59:00
03/07/2007 10:02:00
03/07/2007 10:02:00
03/07/2007 10:04:00
03/07/2007 10:04:00
03/07/2007 10:39:00
03/07/2007 10:39:00
above is the column is a sample from the column i need to be split.
Any help is very appreciated
View 3 Replies
View Related
Sep 8, 2014
I'm trying to build a query that can parse Delimited text to columns, for example I have the following:
ID,Name,Tel,Fax,Email,Directorate,DOB,AOCD,Reg,CD
I would like to convert the above in 10 seperate columnns within a query?
Is this possible? I know you can import delimited text to columns but that is not what i'm after for other reasons.
View 14 Replies
View Related
Sep 22, 2013
I have a problem to count a multi columns into one field. For example, I have a table consist of these fields (simplified version):
- id
- block_name
- handle_type (lookup: big and small)
- kernel_type (lookup: big and small)
- fruit_set (lookup: 1, 2, 3 and 4)
I want to create a query to count a combination of handle_type and kernel_type columns as one column. So the query field would shows these fields:
- block_name
- count_of_big_handle_big_kernel
- count_of_big_handle_small_kernel
- count_of_small_handle_big_kernel
- count_of_small_handle_small_kernel
- count_of_fruit_set_1
- count_of_fruit_set_2
- count_of_fruit_set_3
- count_of_fruit_set_4
View 1 Replies
View Related
Jul 2, 2015
I am trying to create a query that ONLY shows me the total count of each combo box choice for one field. It sounds very easy and I'm sure it is but everything I try tells me I am using the wrong syntax, or requires me to have rows when I only want column totals.
I have a combo box with a dropdown of 10 common places that people travel to. I want the report to ONLY show the total number of people who chose place 1, place 2, place 3... etc. But I always have to do according to date or time or something. I want it to look like this
Place 1 Place 2 Place 3 Place 4
13............36.........3........77
So essentially there would only be one row. But access will not let me. Is there something I'm doing wrong? Or is this not possible?
View 8 Replies
View Related
Apr 9, 2013
I have 1 table that I duplicated to make 3 tables total. I did this b/c I am trying to create separate columns from the same field and table. The field is Workorder. Each workorder list the workorder number followed by a dash and then code. I am wanting to put all of the workorders with the same code in it's own column.
I have 5 codes that i am searching for. The first column list the workorder and a code (123456789-AD). The second column (123456789-BC). I'm good to this point but my problem occurs next.
The third column i am trying to put 3 types of workorder and it's code in the same column. As follows, (123456789-CD, 123456789-TC, and 123456789-PTC. However, when I do this it takes the results from the 3rd column and applies it to the 3rd column but also the 1st and 2nd column. I tried a UNION query and unless I am doing it incorrectly it does not work.
View 3 Replies
View Related
Feb 18, 2015
Produce a query that returns a yes or no (Y or N) field if other text field contains particular text (with wildcard on either side).
Idea is to have a column for each preset word.
View 2 Replies
View Related
May 14, 2014
I have a MS ACCESS 2010 database with a data table which i am trying to create a query from. I have 6 columns of data( one with an ID Field and 5 Name Fields). Below i have made examples of how it first appears as a simple query and the second will show you what i would like it to look like.
What the simple query looks like: [URL] ...
Second what I want the query to look like: [URL] ....
View 2 Replies
View Related
Jan 30, 2008
Hello again!
Hoping to use SQL to do this in a query.
In a table I have linked to a text document (have to do it that way) there is a field that holds multiple values.
Meaning there can be some fields with nothing, some with a -, soem with a 3 digit number, some with a single digit followed by a space and then a 3 digit number, and lastly a 10 character text with spaces between each.
What i want to do in the query is when it looks at that field, create 2 fields out of it.
When its the 5 character value (the one that is like '2 345') split it, place the 2 in the first new field, and the 345 in the second new field. If the value in the originals field is not in that format, jsut carry over the value to the second new field.
does that make sense?? haha
thanks in advance!
View 2 Replies
View Related
Jul 31, 2014
I currently import data into a table and it it has a text field which looks like this: 12,345.67 GBP...I need to use a query to make this a number field so that I can sum it's contents. I've managed to remove the 'GBP' part but can't seem to get rid of the comma?
View 4 Replies
View Related
Sep 28, 2005
I have a field - Period in the format YYYYMM.
How can I split this value so that I only extract YYYY?
Thanks
P
View 2 Replies
View Related
Apr 11, 2008
I have been able to do this with a work around but it is slow and cumbersone when lots of records are involved and am hoping there is a "proper way" to edo it.
--(&100,,,, 200 ---() 300,400- (500),,,, --600
6 is the maximum number of alpha/number entities and down to ----john etc. Result to be
100 200 300 400 500 600
There can also be a mixture as in
--(&John,,,, 200 ---() Andrews,400- (500),,,, --600
John 200 Andrews 400 500 600
View 5 Replies
View Related
Aug 9, 2005
I have a db with a field that I need to split and put values into two other fields. The format of field1 is either a-b, aa-b, aa-bb, or a-bb. I want to take the value before the "-" and put it into field2 and then take the value after the "-" and put it into field3 and then delete field1. Is there a straightforward way to do this? I want to do it in the query design grid so let me know what should be in the "criteria" line versus the "update to" line.
I'm using Access 2000 and have approx 5500 records to convert.
Thanks!!
Tom
View 2 Replies
View Related
Feb 1, 2006
Hi all,
I have a problem this is part of my database
ID NAME DATARANGE ck1 ck2
1 dave 23-Jan-06,24-Jan-06,30-May-06 dataprojector s/table
2 John 23-Feb-06,24-Mar-06,30-Apr-06 s/table
What i would like to do is to run an maketable / updatetable query that splits out the daterange dates into seperate rows and also if there is a value in ck1 or ck2 replace the current value with either a 1 or 0.
So the above table would look like this.
ID NAME DATARANGE ck1 ck2
1 dave 23-Jan-06 1 1
2 dave 24-Jan-06 1 1
.
.
.
?? John 23-Feb-06 0 1
John 24-Mar-06 0 1
ETC
is this possible any help would be greatful
Thanks
Dave
View 3 Replies
View Related
Aug 24, 2004
Lo All,
i need to split records from a field
db - online
table - merk
field - merkCat
in the field i have - 1,2,3
now i send a string bla.asp?cat=1
i want to loop thru the db , but only show the records which actually have the 1 represented
in the merkCat field. Not all fields have that. So far i have selected the db and
i loop thru it , but somehow it wont split up
A little help by pointing me to some cool scrippy would be very helpful
thnx in advance.
View 2 Replies
View Related
Jul 20, 2005
OK Guys,
I am building a sales database for a client he has supplied the text files which I have loaded into tables.
Problem on the sales file there are multiple part numbers in the part field these are separated by '-' the same format is on the Qty and value fields. The logic behind this is:
For the 1st part in the part field the qty and the value are the 1st in there respective columns, for the 2nd part in the part field the Qty an value are the 2nd in there respective columns and so on eg
PS001 |BAGR3-BAGR4-BAGR6 |03-01-01 |4.29-5.17-5.99
How would I get above to look like this:
PS001 |BAGR3|03 |4.29
PS001 |BAGR4|01 |5.17
PS001 |BAGR6|01 |5.99
Any suggestions would be great
thanks
Chris
View 8 Replies
View Related
Jan 30, 2007
Hi
I'm having a senior moment...
I have a reference field - examples below and need to split into the component parts
example Ref_IDs:
C21/0051,Sheet 18, Sheet 19, E 3, 12/01/07
C21/0052.04, Sheet21 ,E 2
C21/0054, Sheet 23, B 2, 05/01/07
First - ref no, Second - Sheet (note some have more than one), Third - Grid and lastly some have dates, but not all
View 6 Replies
View Related
Jun 29, 2015
I am trying to split a field that contains a digit, so lets say the field contains the digit 7, I need it split into 7 fields. How I can do this?
View 4 Replies
View Related
Jun 8, 2006
Hi All
Been handed a contacts database, one field of the DB includes all the industry sectors associated with the customer, with multiple industry references separated only by a comma within the field (e.g. Insurance-Life,Insurance-Broking,Insurance-Motor,Insurance-Pensions,Insurance-Home,Insurance-Health) – I want to split this one field into multiple fields using the comma as the break point (this is something that’s relative easy to do in Excel with the ‘Text to Columns’ tool – but unfortunately it can’t take the 130,000 odd records of the databasel)
Can any one recommend an expression or method that will see the 6 industry references split into 6 separate fields ?
Thanks
Jim
View 1 Replies
View Related
Jul 4, 2013
I have a database composed of personal statistics. (name, age, height, wt, etc). I have two attachment fields. Photos and Videos. Each of these fields can contain more that one file. The size of the video attachments is starting to get me up close to the 2 GB database limit. If each attachment field contained only one file, I would convert the fields over to a path link. I'm stumped on how to move the files out of the main database to control the size, but maintain the multi-file link to my forms. How to restructure this?
View 4 Replies
View Related
Sep 5, 2013
I'm building a query from tables and there is a field i called it: Weight, that should contain the value from the Description field. The Description field is a text field and it always end with the format as --T, with -- as number, and it can be either 1 or 2 digits, no more.
For example: 2T, 48T, 2T, 36T
So, the whole Description field value is always like this: Bla bla bla 2T, OR: heheheheh 24T
The thing is before the --T, sometime people put in 1 space, 2 spaces or multiple spaces, for example:
This is a test [space]6T
This is a difficult test [space][space] [space] [space] 52T
This is an easy test [space] [space] [space] [space] [space] 40T
I hate taking test [space] [space] 1T
So the field Weight should contain the value 6T, 52T, 40T, 1T only
View 2 Replies
View Related
Dec 31, 2013
I am trying to split the year into thirds instead of the quarter. I will be needing to set the criteria to only show me the previous year from the current year. Can this be done in one query?
View 6 Replies
View Related
