Splitting Field - SQL?
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 Replies
ADVERTISEMENT
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
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
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
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 2 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
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
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
Feb 6, 2014
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.
View 1 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
Nov 3, 2005
Getting ready to split a DB. No security really needed... Only the ability for multiple users needed. From what I have read here so far it seems best to use a MDE file on the front end and MDB file on the back end. One question is still not answered... I guess I will find out when I load the front ends on different stations.. BUT... I would like to know what to expect. I am assuming that each computer that I load the front end on I will have to go through and link the backend. Correct? I read a MS Knowledge base article about a form to do this... Is this only possible if you use the "developers edition" ??? Whats the common method for this task?
Thanks
Curtis
View 1 Replies
View Related
Jan 3, 2006
I have created an application that uses all the 'normal' factors of an access app. My forms are triggered by events that initiate some vba code which executes and then does something. No big whoop, we are all doing this; but I am going freakin' insane keeping up with changes. The users are using this app in a "live" test enviroment and the changes/updates are coming in quicker than I can type. "This field is not right it should read like this"
Well I can't change it until everyone gets out - they don't like this answer:D
I jumped in before thinking a few versions ahead and did not split the database :eek: I have read a lot of posts here and other sites and I can tell that I need to split this app but am a bit hesitant. From what I can gather I would have a
Front-End - houses all my queries and forms
Back-End - houses all my tables
I have a few modules, where would they need to go so that I can work on them independent of what my users are doing?
I would give each user a copy of the FE or make it available via network drive; would I then keep a seperate copy of my FE to make changes? If yes, does this mean my modules would be in the FE?
Can I split the db now that it has been in live production? What are some common errors that I should look for prior to?
I tried to split the db one time before, but my drop down list box(s) on the forms would not work. They are controlled by a query, not any code. Error msg stated could not find xyz sorry I don't recall the exact error
I need the ability to change, work on and update at will AND NOT effect my users.
View 3 Replies
View Related
Jul 14, 2005
Hello,
I have been reading a lot about splitting databases on this forum. I still have some questions.
1) Will the FE (Front End) still show the tables?
2) Will users still be able to edit the forms, reports, etc.?
3) Will my code be hidden
4) Will all the users have up to date data showing when they open the Database?
5) Can more than one person open and input data in the database at the same time?
I also want to make an MDE copy, do I split first or make the MDE and then split?
The whole point is the following: I want the people (maximum 10) that will be using this database to only be able to do enter and view data. They should be able to generate the reports but not create new reports. I only want ONE person to be able to edit the forms, code, and reports. How would I do this.
Your help is much appreciated
View 3 Replies
View Related
Aug 11, 2005
I am trying to split my database but when i run the database splitter it comes up with the following error messages:
Subscript out of range
And
Invalid procedure call or arguement
Does anyone have any ideas what could be causing these or how to fix it?
Thanks
View 11 Replies
View Related
Sep 10, 2005
Hi
How can I change the location of the back end part, at the moment the front end is looking for the back end at a different location
Cheers
Bikeboardsurf
View 1 Replies
View Related
Oct 21, 2005
Hi,
I've split a database and the backend relationships are still intact but the front end they are not and it looks like this is causing a problem. Is this usual?
View 5 Replies
View Related
Feb 28, 2006
I've currently got a date of birth field in my database, but would like to query on just the birth month.
Can anyone tell me how to do it. Do I have to create another field which separates out the month, and if so, how do I do that.
Any help would be much appreciated.
View 4 Replies
View Related
Mar 16, 2006
Hi everybody,
I have been spending the last few days trying to find a way through the "access security Maze" which I believe I have just done. I have been following the "Ms Access Security Faq to the letter" and just now, I can open the database via a desktop shortcut including a custom workgroup address and I think everything is working fine. Now, I am trying to organise the next step which is splitting the application.
GHudson stated:
I find it easier to start out with one database that has everything in it
[including the security]. Then I copy the db. Rename them so that one is the
front end, one is the back end. The security will still be in both since they are
exact copies. Then remove the shared tables from the front end, remove
everything else from the back end [except the tables that will be shared].
Then relink the shared tables from the front end to the back end.
Using custom shortcuts will make it easier for the users to open the front
end with the correct workgroup. You can customize the shortcuts target
field with all the required file and workgroup info.
I Understand prtty well what's being said but am getting a bit confused of where and how the different part of the application should be organised on the network:
*The back end file should be on the network drive(drive F)
-That's fine...
*The front end should be distributed only on the station using the application:
1) Does that mean that I could place the FE on the F drive for a short
while and ask the different user to copy the application and past it on their own drive and then delete the FE of the share drive.
2) I will have to use the custom shortcut with the specific work group on each station. Should they all have the same shortcut which would mean that there is only one FE on the F drive and not make much sense or Should the shortcut link to the FE application save on their drive which doesn't make much sense either because they could open the DataBase without the shortcut with their own "default" Workgroup and wouldn't be secure anymore.
I apologize for my confusion and have probably missed a basic step in my reasonning but if anybody could redirect me in the right direction, I would really be gratefull.
View 7 Replies
View Related
May 23, 2006
I setup my security (or lack there of, hehe) and split the database. I relinked the tables.
Now, Access gets hung up when I try to create an MDE of the FE to distribute. It starts the process, asking me to select a folder and file name, I do that, and it gets no further in the process. Access says (not responding).
Should I have created the MDE before I split it? Or does crashing of Access have nothing to do with whether i create the MDE before or after splitting?
View 2 Replies
View Related
