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