Complex Field Splitting Problem
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 Replies
ADVERTISEMENT
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
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
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
Apr 20, 2007
Greetings everyone. I am not an Access Guru so please take me request with a grain of salt.
I am the marketing admin and web developer for the company I work for (co-op telcom). I have built a simple Access database that interfaces with a legacy service order system we utilize company wide. The database simply tracks marketing jobs we work and publishes them as service orders to the system. The problem I have is that I have to format the job number very specifically to fit the legacy systems expected input requirements and I haven not been able to figure out how to do this.
Below is an example of what a series of job numbers must look like. Note the date info preceding each example
Jobs entered on April 20, 2007
070420-01
070420-02
070420-03
Jobs entered on April 21, 2007
070421-01
070421-02
070421-03
The structure of the string is YYMMDD-'Sequential number restarting each day'
The date portion is no problem and nor is the dash. Where my problem lies is in the sequential number restarting each day.
Any thoughts on how to do this. I am guessing this should be some kind of VB or if then statement, but I have no idea where to begin.
To show that I appreciate the help anyone can give with regard to this mater, I am offering a free 1 year hosting package (200M disk space/2 Gigs of bandwidth/mysql/php/unlimited email addy's w/hsphere control panel) to the first person who can help me with this problem.
Thank you for your time and assistance.
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 8, 2006
I have a simple query to calcualte a profit margin on daily sales lines and I use a quick and dirty expression to calculate the margin in the query so I never need to drill it down further than that level (I don't want to go as far as putting the output into a report as it is only for use when double checking lines for errors which get fixed there and then in the database).
So far so good, however the margin output is a bit awkward to read as I can't seem to format it as a simple percentage. The field properties page doesn't like doing anything with the expression and even typing in a format manually has no effect, so I end up with figures like
36.7768595041322
38.6666666666667
15.6448202959831
etc
the expression i use is:
Margin: IIf([dbo_tbl_sales_invoice_lines.price]=0,"",([dbo_tbl_sales_invoice_lines.price]-[net_cost])/[dbo_tbl_sales_invoice_lines.price]*100)
Is there any way to format this output to show only 1-2 decimal places and be in a proper number format so I can sort them in ascending order properly?
View 7 Replies
View Related
Aug 8, 2006
Hi, im relatively new to access but my problem is fairly complex.
I'm creating a database for a school project, and it is focused on improving the way a flying club runs.
I have been able to solve most problems myself, but this one truly has stumped me.
The basics:
There are two tables - Instructor Details
- Flight Completion
And one form - Flight Completion
On the instructor details table i have a field called 'Hours Flown'
The purpose of this field is to show the total hours flown by an instructor.
On the flight completion table i have a field called 'Flight Duration'
This field is for the duration of the flight the instructor just flew.
I want it so that when the instructor presses a button on the flight completion form, the duration entered into the 'Flight Duration' field is added to the 'Hours Flown' field and then the 'Hours Flown' field is updated with the new total.
I hope that is clear to you, but if not please let me know and i will try and clarify it for you.
I really do need help.
Thanks
Jared James
View 2 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
