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.
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)
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.
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)
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.
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:
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!
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.
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 ?
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?
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?
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.
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.
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)
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.
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 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".
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)
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.
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.
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.
