Working with SQL: Extracting Data or Text within a Query, adding new column for extracted data
Home > IT Answers > Development > Working with SQL: Extracting Data or Text wit...
CompEng
600 pts.
0
Q:
Working with SQL: Extracting Data or Text within a Query, adding new column for extracted data
SQL, Access Datasheet, as/400 data, AS/400
I have created a passthrough query in access to draw information from an AS400 system. I have also created some tables in excel and copied and pasted them into access to link them to the AS 400 query via access. So as of now I have three files in one access passthrough query. The one file is " 5000Phantoms79w/VNos&others", the second is "ms0802all79", and the third is "IN014079 woE". The query looks like this:

*SELECT [5000Phantoms79w/VNos&others].PhtomNo, [5000Phantoms79w/VNos&others].OEMno, [5000Phantoms79w/VNos&others].[V PartNo], [5000Phantoms79w/VNos&others].Application, ms0802all79.PRDNO, ms0802all79.DESCP

FROM ([5000Phantoms79w/VNos&others] LEFT JOIN ms0802all79 ON [5000Phantoms79w/VNos&others].[V PartNo] = ms0802all79.PRDNO) LEFT JOIN [IN0140 79 wOE] ON ms0802all79.PRDNO = [IN0140 79 wOE].PN; *

 Now in the file ms0802 there is a table “App.” It lists the description with a Yr attached. The text format of the yearly is not in a specific order. Is there any way to extract the date-year form one table (Column) and create a new column that would then contain that extracted data?



Software/Hardware used:
MS-XP, MsAccess97,AS400 Midrange and Iseires
ASKED: Sep 10 2009  3:29 PM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
RELATED QUESTIONS
0
Philpl1jb
24530 pts.
0
A:
 RATE THIS ANSWER
+2
Click to Vote:
  •   2
  •  0
  • AddThis Social Bookmark Button
Is this correct? There is a text column, and somewhere in this column is a year, and you want to pick the year out of the text. Is that right?

The data in 3 different rows might look something like this:
> here is some text 1985 followed by more text
> this column has lots of numbers and one of them is a date 2001 1985 1234 2222
> 1234 s main street anytown stat


----------------------------------------------------
We can get you the last 2 characters of the string -- if that's the yr great

Select Substring(myString,length(trim(mystring)) -1,2) where length(trim(mystring)) > 1

------------------
To your other question

You don't change queries - you change data in files .. this is built over AS/400 files do you want to
push changes down to them? The pop-ups and advanced logic you're asking for is possible in Access forms with VB coding. Not by a modification of your query.

Phil
_____________

Form CompEng

I am new at this "Sorry"
Below is the query so. Where would the above be entered. I would assume after the last string in the "Select” so, then the where stream. What is meant by the "myString" and "length” and "Trim" and the "Mystring” my string again?
DESCP AS ["MS0802Descp">,Substring(myString,length(trim(mystring)) -1,2)
Where length(trim(mystring)) >

___________________________________________________________________________
SELECT [5000Phantoms79w/VNos&others].PhtomNo, [5000Phantoms79w/VNos&others].OEMno AS ["Database OE">, [5000Phantoms79w/VNos&others].[V PartNo], [5000Phantoms79w/VNos&others].Application, ms0802all79.PRDNO AS ["MS0802PartNo">, ms0802all79.DESCP AS ["MS0802Descp"> **ENTER THe info Here??**

FROM ([5000Phantoms79w/VNos&others] INNER JOIN ms0802all79 ON [5000Phantoms79w/VNos&others].[V PartNo] = ms0802all79.PRDNO) LEFT JOIN [IN0140 79 wOE] ON ms0802all79.PRDNO = [IN0140 79 wOE].PN;
___________________________________________________________________________

So as for the other possible issues!
I don't need to have the number uploaded back to the AS400.
All I need to do is have the number I created in access ….this is it is going to be difficult to explain.
I normally start with a list that was provided to me or I would have a query that would gather all the OE numbers and their associated titles that are assigned to them in the AS400. All this would be in access. I then would take the information and copy and paste it into excel to manipulate the column by using the "Text To Column" feature. This would then separate my Year-Date (if in the right location) and in the other column I would separate my OEM numbers ( the last four digits). Then I would ad a column next to the OEM four digits and do a concatenation to add a prefix number for our numbering system. Now I would have the part number I needed. This would now be copied and pasted back into the Access where I would now do a query that would compare the newly created number to those that are already in the AS400 system. Then I would hopefully get no matches. Which is what I am looking for. For match mean duplications ( this we do not whant). But, when the matches do show. This is when I need to be able to make the changes.
So what you are saying is while the info is in the Access I could learn VB and write something that would compare the data give me a pop up window. The window would contain one of the multiple "My generated" numbers in it. Then I could change the number, press OK and the number would be changed in access. This file is then linked to the query? All I would need to do is after all the numbers are changed in access run the query again and the matches would diminish over time
.
-------------------- Something like the code below which is picking the last 2 characters from ms0802all79.DESCP

SELECT [5000Phantoms79w/VNos&others].PhtomNo, [5000Phantoms79w/VNos&others].OEMno AS ["Database OE">, [5000Phantoms79w/VNos&others].[V PartNo], [5000Phantoms79w/VNos&others].Application, ms0802all79.PRDNO AS ["MS0802PartNo">, ms0802all79.DESCP AS ["MS0802Descp">,

Substring( ms0802all79.DESCP ,length(trim( ms0802all79.DESCP )) -1,2) as YR

FROM ([5000Phantoms79w/VNos&others] INNER JOIN ms0802all79 ON [5000Phantoms79w/VNos&others].[V PartNo] = ms0802all79.PRDNO) LEFT JOIN [IN0140 79 wOE] ON ms0802all79.PRDNO = [IN0140 79 wOE].PN;
------------------------------
Last Answered: Sep 11 2009  4:51 PM GMT by Philpl1jb   24530 pts.
Latest Contributors: CompEng   600 pts., Meandyou   1840 pts.
  •   
0
0
RSS - Discussions Help
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

Philpl1jb   24530 pts.  |   Sep 11 2009  10:40AM GMT

You expect to “find” the year in this list?
2001 1985 1234 2222
You do not know it’s the second item?
First or second would pass tests of reasonable year like with 1234 too early? and 2222 to late?
Don’t see how we can pick the date correctly from that string.
Phil

 

CompEng   600 pts.  |   Sep 11 2009  2:37PM GMT

Below is the question that was asked of me by Phiil.

Is this correct? There is a text column, and somewhere in this column is a year, and you want to pick the year out of the text. Is that right?

Yes to all of the above.

I don’t know what happened to the other conversation that I added to Phiil last comment?
My last comment was that I answered yes to the questions above. I want to show the different types of examples that I experience in the query: ( the what the date is formated ) Also note that all the date is located at the end of the statements, this should help!
1) DODGE RAM TRUCK 8.0L 1996
2) CONCO/INTREP/LHS/VISI 3.5L 02
3) CHRYSLER FAMILY 95-94

The example below was not my examples, but rather the example give to by Phiil asking me if any of the illustration below would exemplify my situations? So my situation is listed above and phiil’s is listed below.
The data in 3 different rows might look something like this:
> Here is some text 1985 followed by more text
> This column has lots of numbers and one of them is a date 2001 1985 1234 2222
> 1234 s main street anytown state

Please note that what I have done in the past I would extract data from a particular column in access by copying and pasting into MS Excel and then delineate using the “Text to Column” format to seperate the date for the other text. Then I would re-paste it back into MS Access. In most cases I would need to manipulate both of the databases to accept my newly created information. Then I would either have to create a new database to import it into my query or link it in. The reasons that I need to find the dates is the file information coming out of the AS400, should follow a required format, but for those that do not this is what creates the difficulty. Dealing with thousands rows or files that are not formatted correctly is what consumes all my time. I need to try to create something that will extract the date styles, then create a new column that would enter the new information into it. Is this possible or not?

Also
On a second note is there a way to choose a particular cell in a column (a give number). Then the number would be made available to be changed? Then it would replaced back into the database/query, the old number would be replaced by the new number. thus updating a text value in a the query. The reason I need to do this is that I need to generate a number for a OEM number (6 to 8 number in any order) base on the last four digits Ex. 124236434 some my number would be 6434. Then when this is ran in the query and compared to existing numbers there may be an othe number 546546434 who’s last four digits are the same. So I want to be able to change the number to the next sequential set in the OEM number? Like 124236434 is not to be 2364. What I would like is that when a match is found a window would pop up with the cell that has my newly generated number in it and I would be able to change the nuumber and press a “OK” button that would replace the old with the new? Then have it updated in my query. This sounds like a tall order but how possible is it?

 
0