Queries :: Alphanumeric String Manipulation - Move Data At The Left To Right

May 23, 2013

I have several thousands of lines of data which I wish to manipulate programmatically, if at all possible. I think that all of the possible permutations are summed-up by the following examples :

123 A text string
2-8 Another text string
A-C Another text string here
3-20 And some more text
3A-126B More text
Some text without any numbers or letters at the left

What I need to do is :

Move the alphanumeric data at the left to the right. So the data would end up looking like this :

A text string 123
Another text string 2-8
Another text string here A-C
And some more text 3-20
More text 3A-126B
Some text without any numbers or letters at the left

I suppose it hinges on identifying where the first space in from the left appears, cutting the string at that point, and

Is this possible, programmatically ?

View Replies


ADVERTISEMENT

Queries :: Create Join Using LEFT String Manipulation

Apr 28, 2015

I think I know the answer but want to check. I've been asked to create a query, without querying a query first, but it's the only way I know.

I have two tables

Table1 will have data in a column that is 9 characters long
ULCABC123
ULCABC124
ULCABC125
PLTABC123
PLTABC124

Table2 will have data in a column that is 6 characters long
ULCABC
PLTABC

Question: Can I create a Join from Table2 Field with the Left(Field,6) from Table1

I was thinking something like this. (but then I can't enter design mode)
Query1 - Test

Code:
SELECT Table2.ORDDETTYPE, Table2.DESCRIPTION
FROM Table2 INNER JOIN Table1 ON Table2.ORDDETTYPE = Left(Table1.ORDERDET,6)
GROUP BY Table2.ORDDETTYPE, Table2.DESCRIPTION;

I presume the only way to do this is first query Table1 (and call Query2) and return the first 6 characters and the create another query (Query3 in this case) using Query2 field joined with Table2 field.

Query2

Code:
SELECT Left([ORDERDET],6) AS NEWORDDET
FROM Table1;

Query3

Code:
SELECT Table2.ORDDETTYPE, Table2.DESCRIPTION
FROM Query2 INNER JOIN Table2 ON Query2.[NEWORDDET] = Table2.ORDDETTYPE
GROUP BY Table2.ORDDETTYPE, Table2.DESCRIPTION;

View 2 Replies View Related

Queries :: Remove Part Of A String From Right To Left

May 28, 2015

I have a manual date format that looks something like:

02/16/2015 09:06:15 AM PST

I would like to truncate the text so that just the date is showing. For all that I have found, I can remove a string from the beginning? any tips on removing a string from the end?

View 6 Replies View Related

String Manipulation: Cleaning Up Address Labels

Dec 28, 2007

PROBLEM: String Manipulation

"Cleaning up Mom's Christmas address labels"

I need guidance on the best string manipulation functions (Instr, Left, Right) to cleanup my mother's Christmas address list of 300+ names.

I have successfully imported the text file into Excel and exported to Access; fieldnames: FULLNAME, ADDRESS, CITYSTATEZIP

I have found instructions on how to breakout FULLNAME field into FIRSTNAME and LASTNAME.

But within the FULLNAME field are many combinations of titles (Mr., Mr. & Mrs., Dr., HON.) with inconsistent periods applied.

Which one of string manipulation functions:

Instr
Left
Right


would be best for extracting these various titles from this name field?

I understand the concepts behind the above functions, but not enough experience using them to understand the tedious syntax or which string manipulation function would be best for extracting the varying title entries to a separate created field called TITLES.

So far, I have deduced this will be a multi-step process. But asking for guidance:

1.) Which string function is best suited for this?
2.) Example of the function syntax for an update query?
2.) Suggested order to administer update queries?...

to extract misc titles from the FULLNAME field.

I am a novice-casual Access user.

Thanks, Greg


(If someone would copyright these steps into a book called "Cleaning Up Mom's Christmas Address List"... I am sure they could retire from sales on Amazon. :-)

View 14 Replies View Related

General :: String Manipulation On A Table - Text Is Gibberish In Group By Query

Jun 22, 2015

While processing string manipulation on a table (140K records, 200-250 MB) the file has corrupted, and I lost all vba Modules, but the data and other DB objects seemed to be in tact.

I now have a query with a text field, when I make a simple join SELECT, the text comes in fine (and, of course, seems fine when presented in table), but when Group by - I get Gibarish: "CARVEDILOL 6.25MG, TABLETS"

Is presented in Group by as: "砅5"

I have recreated the file, importing queries, including this one, but then recreated it as a totally new query, but get that same results.

File Corruption? anything to do about it?

View 12 Replies View Related

Data Manipulation

Oct 22, 2007

Is there anyway to strip all characters except numeric out of a value? For example: 999/777-5555. I want to strip the / and the hyphen. The characters can vary so is there anyway to recognize just numeric?

View 13 Replies View Related

Data Manipulation

Dec 28, 2004

How can two cells be used to calculate any data within a row or column?
For ex. to calculate balance in a database?

View 2 Replies View Related

Parsing The Left And Right Characters Of A String

Aug 19, 2005

I have the following string: NE1220904
I need to remove the left two characters and the right four characters of a string in a query. I have found how to remove one side or the other, but not from both sides. Thanks in advance for the help!

View 6 Replies View Related

Left Three Characters From String Using Query

Sep 8, 2005

In MySQL I can use the LEFT(3, field_name) command, but I essentially want access to pull all records from a table where the the first three digits of a number field match those pre-selected by a form drop down.

This database is in Access 97. Is there a way to compare the first x number of digits using SQL only, or do I need to run each line through code first, and then check it (I really don't want to do that)?


example numbe rin field:

123456789

SQL checks to see if 123 matches value selected on a form.

View 1 Replies View Related

Queries :: Move Data From One Table To Another Automatically

Jan 29, 2014

I have a Macro Which my team members fill and submit the information of their daily workload. And the person who Submits has a Pivot table to check if the Workload that he has submitted has been QCed or not.

I have made 2 Tables one is temporary database and other is a master Database, I keep clearing the Temp Database because when users refresh the Pivot table it takes less time if there is less data and Vice Versa.

Problem Statement: When a particular Column is updated for example "QC Result" column is updated as "Green" or "Red" the entire column needs to be moved to the Master Table.

View 1 Replies View Related

Data Manipulation Query

Oct 29, 2007

Dear all

I wonder if anyone could advise?

I routinely have to manipulate antibiotic testing data as part of my job. The data takes the form...

OPIE idAntibiotic nameSusceptibility result description
ID1AmikacinSusceptible
ID1AmpicillinSusceptible
ID1CefotaximeSusceptible
ID1CeftriaxoneSusceptible
ID1CefuroximeSusceptible
ID1CephalexinSusceptible
ID1CephradineSusceptible
ID1ChloramphenicolSusceptible
ID1CiprofloxacinSusceptible
ID1ColistinSusceptible
ID1FurazolidoneSusceptible
ID1GentamicinSusceptible
ID1High level ampicillinSusceptible
ID1High level streptomycinSusceptible
ID1High level tetracyclineResistant
ID1KanamycinSusceptible
ID1Low level ciprofloxacinSusceptible
ID1Nalidixic acidSusceptible
ID1NeomycinSusceptible
ID1SpectinomycinResistant
ID1StreptomycinResistant
ID1SulphonamideResistant
ID1TetracyclineResistant
ID1TrimethoprimSusceptible
ID2etc

...which I have to crosstabulate, reorder, recode and place in a new table.

Currently I use two queries in Access to do this, but it takes a long time as there are invariably a million or so records.

I've tried to use VBA to speed things up, but with limited success.

Firstly I tried to do a cross-tab and append for each unique identifier...

Code:Sub PopCrossReorderRecode1()Dim db As DatabaseDim rsAbres As RecordsetDim rsCrosstab As RecordsetSet db = CurrentDb()Set rsAbres = db.OpenRecordset("SELECT [OPIE id] FROM Abres GROUP BY [OPIE id]")For Each Value In rsAbresSet rsCrosstab = db.OpenRecordset("TRANSFORM Max(Abres.[Susceptibility result description]) AS [MaxOfSusceptibility result description] " & _ "SELECT Abres.[OPIE id], Max(Abres.[Susceptibility result description]) AS " & _ "[Total Of Susceptibility result description] " & _ "FROM Abres " & _ "PIVOT Abres.[Antibiotic name];") DoCmd.RunSQL "INSERT INTO CrossReorderRecode ( [OPIE id], Tested, Ak, pA, Ctx, Ctr, Cfx, Cx, Cfd, C, Cp, Co, Fu, G, A, S, T, K, CpL, Nx, Ne, Sp, pS, Su, gT, Tm )" & _ "SELECT rsCrossTab.[OPIE id], rsCrossTab.[Total Of Susceptibility result description], " & _ "rsCrossTab.AMIKACIN, rsCrossTab.AMPICILLIN, rsCrossTab.CEFOTAXIME, rsCrossTab.CEFTRIAXONE, " & _ "rsCrossTab.CEFUROXIME, rsCrossTab.CEPHALEXIN, rsCrossTab.CEPHRADINE, rsCrossTab.CHLORAMPHENICOL," & _ "rsCrossTab.CIPROFLOXACIN, rsCrossTab.COLISTIN, rsCrossTab.FURAZOLIDONE, rsCrossTab.GENTAMICIN, " & _ "rsCrossTab.[HIGH LEVEL AMPICILLIN], rsCrossTab.[HIGH LEVEL STREPTOMYCIN], " & _ "rsCrossTab.[HIGH LEVEL TETRACYCLINE], rsCrossTab.KANAMYCIN, rsCrossTab.[LOW LEVEL CIPROFLOXACIN], " & _ "rsCrossTab.[NALIDIXIC ACID], rsCrossTab.NEOMYCIN, rsCrossTab.SPECTINOMYCIN, rsCrossTab.STREPTOMYCIN, " & _ "rsCrossTab.SULPHONAMIDE, rsCrossTab.TETRACYCLINE, rsCrossTab.TRIMETHOPRIM " & _ "FROM rsCrossTab;"NextEnd Sub

...but just got a run-time error 3251 or an error with the transform statement.

Next I tried to address each antibiotic at time...

Code:Sub PopCrossReorderRecode2()DoCmd.SetWarnings off' Delete the current contents of CrossReorderRecodeDoCmd.RunSQL "DELETE CrossReorderRecode.* FROM CrossReorderRecode;"'Populate OPIE id in CrossReorderRecode from AbresDoCmd.RunSQL "INSERT INTO CrossReorderRecode ( [OPIE id] ) SELECT Abres.[OPIE id] FROM Abres GROUP BY Abres.[OPIE id]", dbOpenTable' Run an update query for each antibiotic, putting the relevant code into the relevant field'AmpicillinDoCmd.RunSQL "UPDATE CrossReorderRecode INNER JOIN Abres ON CrossReorderRecode.[OPIE id] = Abres.[OPIE id] " & _ "SET CrossReorderRecode.pA = IIf([Abres].[Susceptibility result description]=""RESISTANT"",""A"", " & _ "IIf([Abres].[Susceptibility result description]=""INTERMEDIATE"",""pA"", " & _ "(IIf([Abres].[Susceptibility result description]=""SUSCEPTIBLE"","""",""."")))) " & _ "WHERE (((Abres.[Antibiotic name])=""AMPICILLIN"")); "'High level Ampicillin'etc etc

...and whilst this worked it took twice as long as the original query, which I imagine is do to its repetitive nature.

If anyone is able to point me in the right direction I'd be grateful. I'm relatively new to VBA and finding it useful and frustrating in equal measures!

Thanks,

Iain

View 2 Replies View Related

Queries :: Join Two Tables For Further Manipulation

Feb 1, 2015

I have two tables: products and orders.I would like to query the products only with orders using the product code and/or model number and join up all product and order information for further manipulation.

View 2 Replies View Related

Modules & VBA :: Body Of Outlook Email - Left Justify String

Mar 5, 2015

I have the following string of text that I currently push out to the body of an Outlook email (using the DoCmd.SendObject function with an Access report):

Code:
sMsgBody = "Please find attached the specified Final Costing Report for WO# " & WO & vbCr & vbCr
sMsgBody = sMsgBody & "Dealer: " & rs.Fields(0).Value & vbCr
sMsgBody = sMsgBody & "Model: " & rs.Fields(1).Value & " : " & rs.Fields(2).Value & " : " & rs.Fields(3).Value & " : " & rs.Fields(4).Value & vbCr
sMsgBody = sMsgBody & "Margin $: " & rs.Fields(5).Value & vbCr
sMsgBody = sMsgBody & "Margin %: " & rs.Fields(6).Value & vbCr

Which is great and works fine! It appears in the email like so:

Original.PNG

Now, it has been requested that the data to be left justified to the widest line... so, in this scenario, it would look like this:

Edit.PNG

I've searched for ways this might be accomplished so it's always left justified to the widest line, but haven't come across any definitive solutions yet.

View 9 Replies View Related

Modules & VBA :: CSV File Manipulation - Parsing Data Into Array

Jun 9, 2013

I'm trying to parse the following into an Array by splitting the csv file using a "," comma separator. There should be 63 different data pieces in this File. When I do a count of them from the (ubound array) i only get 54. The last data piece on each row gets concatenated to the first data piece of the next line. Is there a way to stop this from happening? This is causing problems with working with the data.

Date,Open,High,Low,Close,Volume,Adj Close
2013-06-07,1625.27,1644.40,1625.27,1643.38,3371990000,1643 .38
2013-06-06,1609.29,1622.56,1598.23,1622.56,3547380000,1622 .56
2013-06-05,1629.05,1629.31,1607.09,1608.90,3632350000,1608 .90
2013-06-04,1640.73,1646.53,1623.62,1631.38,3653840000,1631 .38
2013-06-03,1631.71,1640.42,1622.72,1640.42,3952070000,1640 .42
2013-05-31,1652.13,1658.99,1630.74,1630.74,4099600000,1630 .74
2013-05-30,1649.14,1661.91,1648.61,1654.41,3498620000,1654 .41
2013-05-29,1656.57,1656.57,1640.05,1648.36,3587140000,1648 .36

View 3 Replies View Related

Importing Alphanumeric Data Into Text Field

Mar 31, 2008

I have searched this forum but didn't find the answer, I hope I'm not the only one with this problem.
I have two text fields in a table that are updated through an excel file import. In the excel file, both fields contain data that is mostly numeric, but there are always about a quarter that contain letters as well. When I go to import the excel file, it sets to null any value in the fields that contains letters. If I sort the excel file in descending order for the field, it will import, but I have two fields that do this, so this brings more problems. Does anyone know why you can't just import anything in any order into a text field?

View 14 Replies View Related

Queries :: Extracting Data From Unusual String

Mar 5, 2015

I have a flat file sent to me today that contains a number of fields with the same issue. As an example, I have a field named Coordinators from a table named Audit and the data within it is formatted as follows:

Mark Hollings;#14664;#Judy Thompson;#10169;#Tammy Wilson;#3608;#Tim Levy;#2785;#David Simpson;#1251

Is there any way to extract only the names while leaving in either the ";" or replacing it with a ","? My desired output would look like this:

Mark Hollings;Judy Thompson;Tammy Wilson;Tim Levy;David Simpson

This field can be populated with one or more names so that's a variable in all of this as well.

View 3 Replies View Related

Queries :: Expression / Criteria To Return Alphanumeric Values Only

Jun 30, 2015

How to write an expression/criteria in a query that will return only ID numbers with alpha numeric values. Example: My Id field contains both numeric 479621, 680530 and alpha numeric 132NAV100, 174NAV26a values. I want to run the query to return only the alpha numeric values.

I do not know SQL nor VBA.

View 11 Replies View Related

Forms :: Alphanumeric Data In Field - Multiple Auto Number

Dec 27, 2013

I have a table having the following fields:

StaffNo TextField PrimaryKey
Name
....
...

The data in StaffNo will be alpha numeric, like AKA-111, AKA-112, LMN-100, LMN-102

Here AKA and LMN describes the Sites where employee is working.

On Add New Employee, When user enter AKA- in StaffNo, on exit the next number on that site should be generated. i.e AKA-113.

View 8 Replies View Related

How To Move Data From One Colum By Data Position To Other Fields

Dec 19, 2006

Hello Tech gurus,

I have a database with existing data, that is not normalized, and all the data is in one field...:eek: This what I am dealing with:

As you notice the first row of data in field1 C10A CHOLEST&TRIGLY has 3 spaces to the right the next row ALTORVASTIN has five spaces to the right, the next line LIPITOR has 7 spaces to the right, and so forth, hopefully you get at what I am trying to do, I just looked at the data and it is not showing up in view of the leading spaces, but I am trying to move the data based ONLY one the position of leading spaces, example all data that has 3 leading spaces would go into its own separate column, and all data that has 7 leading spaces would go into a separate column, I have tried to use the left, mid, len functions but I cannot figure out how to move the text and keep it whole only by data position of spaces.

Field1:
C10A CHOLEST&TRIGLY
ATORVASTATIN
LIPITOR
PFIZER
SIMVASTATIN
SIMVASTATIN TEVA
TEVA
M1A ANTIRHEUMATIC N-STEROID
ETODOLAC
ETOPAN
TARO PHARMA
Thanks so much for your help... :confused:

Nana :D

View 12 Replies View Related

Data Base Left Open

Feb 1, 2008

How can I stop agents from leaving a database open after they have finished there days work. I maintain and modify a database from home, but some agents forget to close the database at the end of the day. That prevents me from doing any modification on the database. Maybe a simple message that would come up and close on a regular timed bases. Or some other simple way of reminding them to close before leaving for the night.

View 9 Replies View Related

Queries :: Sum Two Fields On Left Join?

Jul 29, 2013

I currently have two tables each containing information that I need to complete a query, however the results of suming this information is incorrect.

Below is the structure of the table, one contains more fileds than the other however the joins are on the Date and the Name fields. I have added a few dummy results to show the format

Table 1
"Date" "Name" "Times Logged In"
10/01/2013 Chris 1
11/01/2013 James 3

Table 2
"Date" "Region" "Manager Name" "Name" "Times Logged Out"
10/01/2013 Scotland Cindy Chris 1
11/01/2013 Wales Robert James 2

Query Fields

"Data" "Region" "Name" "SUM Times Logged In" "SUM Times Logged Out"

I can see what the issue is, if someone hasnt logged out there would be no entry in the table so the result query would show "Times Logged In" as X and a blank value at "Times Logged Out." However if there is a value at "Times Logged Out", "Times Logged In" is displayed again and causes the SUM calculations to be incorrect.

how do I overcome this issue though? I can do two spereate queries on the tables to produce results for Times Logged In and Times Logged Out but cant seem to create one query to display both these results in one table.

What I want is IF the date in Table 2 = Date in Table 1 AND IF Name in Table 2 = Name in Table 1 THEN take the value from Logged In and place that in a column and the value from Logged Out and place that in a column.

View 2 Replies View Related

Queries :: Left Join On 3 Fields

Mar 21, 2014

I'm having problems doing a left join with 3 fields. I know I don't need the 3 fields for this example to align the data but, I deal with much more than this and need this functionality.

The query object I'm looking into is named 'left join' and the sql is as follows:

SELECT Act_Base.CustID, Act_Base.Chipset_ID, Act_Base.Product, Act_Base.Qtr, Act_Base.Qty AS Shipments, SE_Base.Qty AS SE
FROM Act_Base LEFT JOIN SE_Base ON (Act_Base.Product = SE_Base.Product) AND (Act_Base.Qtr = SE_Base.Qtr) AND (Act_Base.CustID = SE_Base.CustID);

I'm expecting to see 150 units in the SE.Qty field.

[URL] ....

View 5 Replies View Related

Queries :: Get All Text To Right Of First Left Space

Mar 21, 2014

how to find the first space on the left of a text string, for example:I have this string : FUJIFILM XM-1 16-50mm BLACK and I want to find the space between FJUJIFILE and XM and then get all the text from XM onwards.I have this expression that gets me so far but then I'm stuck:

Code:

Model: Left([strDescription],InStr(1,[strDescription]," ")-1)

This expresssion gets me FUJIFILM, which isn't want I want.

View 6 Replies View Related

Extract Data To Left Of Dash In Query

Jun 20, 2005

I need to break up part of the value in one of my fields. I can do it in Excel by identifying the position of the dash with eg. =FIND("-",D13) And then using it in a LEFT function. Or using the text to Columns wizard.

How can I do it in a query, there is no find function. If there is an equivelant what is it?

Sample data:

22DF-RED
33AF-0
44TG-Blue


I need only the data to the left of the Dash

Thanks

Ziggy

View 2 Replies View Related

Queries :: LEFT JOIN On Nearest Date

Apr 2, 2013

I need to do a report that takes data from many different tables (like 10).

To do this I have a big query with many LEFT JOIN to link all the tables together. I have made this query last year and it worked well and efficiently until now.

But I have to add something there, and the problem comes with it :

One of the tables is a currency exchange rate table, with the exchange rate of USD to 50 different other currencies and for each day since Jan 1st 2011.

But in this table there are some dates when a currency does not have an exchange rate (example : it was a holiday in that country, so there is no official exchange rate with this currency on that day).

However, I need to have rates even if they are not present in the table.

I need to get the nearest rate, for example if the rate of EUR on 31st December is not available, I can take the one of the 30th, or 29th or January 1st...

Ideally I would like to do the following :

Add a new LEFT JOIN to my already big query that would look like :

LEFT JOIN CurrencyExchangeRate ON ABS(RateDate - Instrument.Date) < 3

But to take only the closest rate.

I tried with a subquery :

LEFT JOIN (SELECT TOP 1 * FROM CurrencyExchangeRate WHERE ForeignCurrency = Instrument.Currency1 AND ABS(RateDate - Instrument.Date) < 3) AS MRate ON MRate.ForeignCurrency = Instrument.Currency1)

But the subquery does not accept Instrument.Currency1 because it is not part of the table CurrencyExchangeRate

I have a solution that should work, it is to search for this rate in a subquery in the select clause instead of doing a left join. But the tables are quite big (hundreds of thousands of entries) and my work machine is not really 'fast' so it would take hours and hours (when I tried I stopped after 3 hours).

I know there is the solution to make a macro to "fill" the missing rates with the rate of the previous day... But I would prefer not to go to this extent.

View 2 Replies View Related

Queries :: Left Function With 2 Static Characters

Jul 23, 2015

I have to query a record with 2 of the same static characters. "-".I can get the left function with the first " but I can't get the rest up to the second "-"

Example: B-4352B-PXP02W01-10

TagNo: Left([EventInstanceID],InStr([EventInstanceID],"-")) Brings me the first char plus "-" The next section is needed also but the remainder is not.

Needed: B-4352B

Is there a Mid Function that I could add to go to the second chaacter instead of stopping at the first one?

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved