Split Up Data In One Field Into Several Fields?
Feb 24, 2012
I have a field in an Access 2003 table that has several image names in it separated by "; " (semi colon and space). The thing is, I need to split them up into their own fields..My table name is "ONE BIG TABLE" (will be exporting data from one table for CSV).My starting field name is "ALT_IMG".An example of the contents of a record within "ALT_IMG" is
Code:
/AAG70260G05_2_1.JPG; AAG70260G05_3_1.JPG; AAG70260G05_5_1.JPG; AAG70260G05_6_1.JPG; AAG70260G05_7_1.JPG; AAG70260G05_8_1.JPG; AAG70260G05_4_1.JPG
What I need is to split these up into their own fields. I can create new fields to populate, I just need to get them in the fields and to remove them from the original ALT_IMG field after moved. There can be as many as 0 or 1 to 20 images in the ALT_IMG field.
Ultimately I was thinking about making new fields named "ALT_IMG_2", "ALT_IMG_3" (up tp 20) and then making an update query of sort to anything more than 1 image to the next field. Meaning if there are two images, then the first stays where it is and the second is moved to ALT_IMG_2. If there are three then the first stays where it is, the second goes to ALT_IMG_2 and the third goes to ALT_IMG_3.Now I know that there are benefits of having multiple tables but I need this to end up in the same table.
View Replies
ADVERTISEMENT
Sep 3, 2013
I have an access table that looks like the below:
"field1", "field2, field3, field4, field5, field6, field7"
"field1~field2~field3~field4~field5~field6~fie ld7"
"field1~field2~field3~field4~field5~field6~fie ld7"
"field1~field2~field3~field4~field5~field6~fie ld7"
"field1", "field2, field3, field4, field5, field6, field7"
"field1~field2~field3~field4~field5~field6~fie ld7"
I've got a module which loads the data into a table, but it can't handle the records that are ~ delimited, so spits them out as a single field.
I know there is some VBA code that can be used to split comma delimited records, and I've seen bits of it floating around online and tried to alter it to work for the ~, but I get the feeling that what I've seen is only a part of the required code, how to put it all together to make something that actually works.
So, what I need to do is;
- Split some records in a table out into multiple fields where there is a ~ present
- Place these newly split fields into a table (I don't mind if it ends up in a new table or not)
View 7 Replies
View Related
Aug 28, 2006
Hey,
The database I am working on, I split a while ago to give it some security. Now i'm updating a related form, and i'm finding that if I delete and add fields in the BE, the FE fields (being the fields that I need to insert into the form so the data entered propogates to the DB) are not updated.
How do I update the FE?
Thanks.
View 1 Replies
View Related
Jun 22, 2005
I would like to split the contents of one field (in a table) into two or more. How can I do this?
e.g. 1 Coca-Cola Australia becomes 1 (in one field) and Coca-Cola Australia (in another field)
thanks :mad:
View 1 Replies
View Related
Jul 30, 2013
How to split characters:
Example: my expression is
2+12*1+10
I want split this and put it to 4 text field
field1: 2
field2: 12
field3: 1
field4: 10
View 3 Replies
View Related
Apr 20, 2013
I have done this in Excel before, but not Access. I do not know VBA. I figure this will have to be done in a query or a macro. I don't even know if all of this is possible in Access. I need to be able to split an Address field into:
Street Number
Street Name
Street Type
Street Direction
And the purpose of this is so that I can pull out the Street Type (Drive, Road, Lane, etc) and update the abbreviations (DR, RD, LN) to the Street Type spelled completely out.
I did this in Excel by creating a Named Range "Types" on a sheet that has the abbreviations in Column A and the spelled out versions in Column B so that I could convert the abbreviations to complete street types. I broke down the entire address into each part on another sheet. Then I did a VLOOKUP to look up the Street Type in Column B in the Types range. Works great! And the only way I knew at the time to do that.
But, by doing this, I have to get the data I need from a download into a spreadsheet, break the address fields down on a second sheet, do all kinds of field update conversions (to get the field names from the download to match the field names in my Access table). This takes extensive Excel programming. I just thought there might be a way to do it by simply importing the data from the download straight into Access. That is easily done, but the Street Types aren't consistent.
There may even be a better way to do this than splitting....something like if a field CONTAINS DR update it to Drive. This would be a long process to set up because there are so many different street types to consider.
This is basically for the purpose of finding duplicates. If there is one entry called 123 Main St and another called 123 Main Street, they are not going to show up as duplicates, rather as two separate records.
View 2 Replies
View Related
Apr 30, 2014
I have a split form with 10-15 fields. Each of these fields is a combo box. The trouble I am having is sorting the data in the datasheet of the split for. I want to sort ascending by "ProductTypeName", then ascending by "Parent SKU" and then ascending by "ProductSizeID".
How can I do this when each of my combo boxes have an ID and then a name? Example below:
ProductTypeID
ProductTypeName
1
Apron - Bib
2
Apron - Waist
3
Bag - Backpack
4
Chef Coat
[Code] ......
View 3 Replies
View Related
Mar 18, 2005
I am new to Access and was wondering if there was a way to parse the data from a field into two fields.
I have a field named tName which contains both the first name and last name of a person.
How can I get the corrosponding last name into a new adjacent field called tLastName (this field doesn't exist yet)?
Thanks so much in advance,
Paul
P.S. What if the the person doesn't have a last name? or if they included a middle initial?
I know, it's messed up. This DB has a bad setup.
View 2 Replies
View Related
May 18, 2005
Hello,
I have a few tables containing different exam data for students - and a unique identifier with each. I need to add another unique identifier (which is already specified from another system).
Now, can I use the existing identifier (UPN) to automatically put in the new identifier into a new field (AdNo).
If that makes any sense at all, I will be surprised...thank you!
View 1 Replies
View Related
Feb 27, 2008
Hi,
I have a table that is used for project progress. One of the fields is named "Completed". I would like to know if it's possible to auto fill the data in the "completed" field based on three other fields within the same table.
ex: If field 1 = Yes, and field 2 = Yes, and filed 3 = Yes, then field "completed" = Yes
I'm open for any ideas, I'm new to access and am not sure how to get this started.
Thanks
View 3 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 23, 2006
I trying to create a query that looks at 2 field in one table and give me the resolves in a new field.
Example
Field 1 Field 2 New field
Hat Cap Cap
Dress Dress
Pants Slacks Slacks
Gloves Gloves
Socks Socks
Can you help?
View 4 Replies
View Related
Apr 25, 2014
I have a table "MansComps" which includes two fields, the "Index" field which contains a number, e.g "5" and the "Tabletype" field which contains a letter e.g "T". i have created a query based on this table, which combines these two fields and displays the result to a third field which I call "Components".
field: Index
1, 2, 3,....
field: Tabletype
T
field: "Component" has to be like this:
T00001, T00002,...
I have managed to combine the fields in my query using the expression:
Component: [Tabletype] & " " & [Index]
but the results appear without the leading zeros, like T1, T2..I have set my Index content to be a number and as a primary key and the Tabletype as a text. I also tried to change the format of Index to "00000" which works for the Index field separately but not for the Component field.Should I do something in the format of the Component field?
View 4 Replies
View Related
Feb 19, 2013
Let's say I have a table called "Courses" which among other things has "Completed"(Yes/No) and "Category"(Text).
Now, there also another table "Progress" which has similar columns.
I want Progress.Completed to reflect if all courses of the specified category have been completed.
How would I do that? I'm new to access ...
View 1 Replies
View Related
Jun 15, 2014
I have a notes field in the customer table that is a memo field. An example of one customer's notes field data :
<div>20.3.14 Ordered 2 cartons</div>
<div>4.3.14 Ordered 2 cartons</div>
<div>18.2.14 ordered 1 carton</div>
<div>30.1.14 ordered 3 cartons SCENTED wipes</div>
[Code] ....
I want to extract the date to append to a date field in a "Calls" table and the comment into a text field in the "Calls" table. Is there a way I can do this via query or code?
View 2 Replies
View Related
Aug 7, 2014
I'm trying to create a system where if I enter data into one field it will automatically appear in the corresponding field in another table. For instance if I enter the values 10,12,15 into a field called QID in table A I would like this to appear automatically in a field called QID in table B. Both tables are related and I have enforced referential integrity and 'cascade' options but this still doesn't seem to allow one table to automatically update another. My ultimate aim would be to have a form that you filled in data for the field QID once that then propagated to both Table A and Table B.
View 2 Replies
View Related
Feb 18, 2007
I have a table that holds 250 records, and dont really want to split manually as the saving grace is that each record, has a hyphen that marks the point where the split needs to take place.
For example, the field currently holds
'Movicol sachets - 1 or 2 sachets dissolved in water at 21.30'
That is stored in 1 field, I now need to be stored in to 2 fields, whilst removing the hyphen, and spacing immediately before and after so it works out like this.
Field 1 = Movicol sachets
Field 2 = 1 or 2 sachets dissolved in water at 21.30
I know it can be done using either a query or VBA, but I am not sure which function/procedure/command I need to use to carry this out. Any assistance would be gratefully recieved.
Thanks.
View 7 Replies
View Related
Jan 28, 2005
A pre-existing table was created with lastname, firstname contained in one field. I would like to create a new table with a separate field for lname & fname. Is there a way to take the existing names and put them in separate fields? (Some contain a middle initial as well)
View 2 Replies
View Related
Dec 28, 2007
Trying to split
CASE/MEMBERID field 00041130P10005573
into
CASE 00041130P
MEMBER 10005573
Would someone please show me how to do this? Thanks
View 4 Replies
View Related
Apr 28, 2008
I have a name field in this format: smith, john
Is there a way to split this into 2 separate fields in a query?
I looked up the split function, but it's saying that this function doesn't exist.
I also thought this might work, but it doesn't.
SELECT left(name,instr(name,",")-1) as LastName, right(name,instr(name,",")+2)from table1
Can someone tell me what I'm doing wrong or recommend a way to do this?
Thanks.
View 3 Replies
View Related
Aug 31, 2006
My situation is this. I have 3 tables that I have imported from my mainframe system, between these 3 tables I have the data of product code, description,supplier code, supplier name, order method, and ABC code.
I am trying to create another table that I can capture daily Out Of Stock data for products.
What I would like to do is to enter the product number in the first field of my new table, and then the remaining fields will auto populate with the correct details based on the product data stored in the parent tables that I have imported.
How can this be done?
View 3 Replies
View Related
Sep 9, 2013
How to disable fields in Access based on data entered in a previous field? For example if "yes" is chosen from a drop down show "Date field" if "no" is chose hide "Date Field".
View 2 Replies
View Related
Oct 12, 2004
This has to be a common problem.
I have an environment where I have two databases. One holding tables (The Data) and one holding everything else "The Programs" (Forms, Queries etc)
When the application gets enhanced I currently have no problem updating "The Programs" since I can just copy the new MDB on top of the old MDB.
This is not the case with "The Data", if I need to change any of the tables (since they contain live data, that is modified all the time) I have to physically connect to the machine holding the Data.MDB and modify the tables. The problem is there are getting to be many locations where these changes need to be made.
Is there any way to update the tables from a script (I used to use SQL to update remote tables in a mainframe world)
Thanks
View 3 Replies
View Related
Mar 1, 2007
I have a data base which is lying on server - back end and front is on 17 machines. Because of some firewall restrictions, I have to put front end on user's machines for better connectivity. Now the problem is quite oftenly server IP changes and because of that I have to roll back all the tables and split the data base again because tables are linked thru server IP.
Is there any method to autoupdate server IP for connectivity? Or I am thinking if there is a way of making a field in one table in which I can put / change IP and automatically link is created/changed.
View 7 Replies
View Related
Apr 15, 2008
I have a field in access database called result (coming from webform) and the content is something like: 'not much; 2' or 'frequently; 5'. How can I split numeric and text value into two separate columns in query?
I will greatly appreciate any help.
Debbie
View 14 Replies
View Related
Nov 11, 2013
I have been given the task of organising a mail-merge with a sharepoint list, but the names and emails attached to each object are seperated by a delimiter. Furthermore each person is associated with many objects, and they want the merge programmed to only send 1 email to each person.
So what I need to do is split the data in one column into three separate columns, and then perform a concatenate. The concatenate isn't an issue, but how to split the data into 3 new columns within Access?
If worst comes to worst I'll tell them they will have to use the text-to-columns function in excel first, but would like to try and avoid that where possible.
View 4 Replies
View Related
