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 RepliesIs 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.
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
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!
I have a field - Period in the format YYYYMM.
How can I split this value so that I only extract YYYY?
Thanks
P
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
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
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
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.
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
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
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 RelatedHi 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
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 RelatedI 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!
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.
I am new to MS Access, and am not sure if what I want to do is even possible. It is my understanding that comparing subsequent cells within a field in a database cannot be done- so I thought I'd see if there is a way to go around it.
I have a dataset for pedestrian activity, with over 3 million rows and 40 columns - too big for excel to handle.
I need to sort the entire dataset by 2 fields, following which I need to search down the field containing my pedestrian ids (numbered 1, 2, 3... till approx 10000), and when my ped id changes from one to the next, I need to check the value in a field showing the ped location, and if that matches with the ped's previous location, I need to copy out a cell corresponding to the previous cell's time stamp. If it doesn't match, I need to copy out another time stamp from another field.
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 RelatedHello,
I am trying to make a new form with the same info as another but in a different view for easily updatable forms/reports. The current table i have looks like this:
Company Product Market Available?
1................1................2..........yes
1................5................2...........yes
2................1................1...........yes
2................2................6............yes
etc... with the numbers linked to tables with the actual name.
what i want to do is make a table with field names that correspond the different products so that it looks like this:
Company Market Product 1 Product 2 Product 3
1.............2............yes..........yes....... ......no
2.............1.............yes.........no........ ........yes
1.............4..............no.........no........ ........yes
Ive played around with crosstab queries but I'm not getting the results i want. Is there any way to have this new table linked to my first table so that if theres a new product # entered it will automatically make a new column on the new table and fill it in? Let me know if this is too confusing, Thanks for your help.
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
i want to display a single field values in list box with query as row source.the query will produce variable number of records or rows. sometime 1, sometimes 3 or 6 my thought is to distribute these records/row into list box column. for example:
if query = 1 then listbox column = 1
if query = 6 then listbox column = 3 (two rows per column).
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?
I have an excel sheet, sent to me by someone else. It contains a column of unique information (site IDs), followed by three columns which each contain either a unique value or a blank, something like this:
Code:
sID|Code1|Code2|Code3
011|1234A|1234B|
021| |2345B|3456C
031|3456A|3456D|
041| | |7890E
The ID represents a particular business with which we interact, whilst the codes represent particular types of contract that we have with them.
This is then followed by a lot of columns containing information about each entry, name, address, post code, contract data etc.
I need to relate this to the employees at those businesses, whose data I have recorded in a separate spreadsheet, so that addresses and such are also related to people, not just the business unit.
This spreadsheet records each person by employee ID (a unique value for each entry) and Code, without taking account of whether it is a code of type 1, 2 or 3. Like this:
Code:
eID|CodeX|FirsN|SecoN
011|1234A|Teddy|Bears
021|1234B|Harry|Horse
031|3456A|Edwin|Eagle
041|3456A|David|Tyrex
I figure the best way to create a relationship between these tables once imported into access is via a third table containing a column with all the Codes in a single field and a second field with the sID related to each code.
Code:
sID|CodeX|
011|1234A|
011|1234B|
031|3456A|
041|7890E|
My question is, how do I get all the Codes from the three different fields into a single field and relate it to the relevant sID for each?
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.
How to display in a report a field with 50 records in two separate columns of 25 records.Is this possible or am I completely off the reservation?
View 2 Replies View RelatedI have a multiple select combobox which shows up in many forms- SubCategories. The user can pick from a list of 154 SubCategories.
Everything is working properly however it is difficult for the user to scroll through the entire list.
My question: Is there a way to represent this ONE field in multiple columns in the combobox?
I know how to add columns for multiple fields to my combobox, but this is one field.
Maybe there is a better way to represent the data... a subform would have the same issue.