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 Replies
ADVERTISEMENT
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
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
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
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 8 Replies
View Related
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
Mar 17, 2008
This is probably very simple but still beyond me.
I have a table that stores text in a number of different languages. Each entry has a unique ID number. Each language version of the same text shares the ID number.
I have a column of tick boxes that show where this text is used.
ie
1.0 English_text_record_1 tickcol1=y tickcol2=n tickcol3=y
1.0 French_text_record_1 tickcol1=y tickcol2=n tickcol3=y
1.0 Spanish_text_record_1 tickcol1=y tickcol2=n tickcol3=y
2.0 English_text_record_1 tickcol1=y tickcol2=y tickcol3=y
2.0 French_text_record_1 tickcol1=y tickcol2=y tickcol3=y
2.0 Spanish_text_record_1 tickcol1=y tickcol2=y tickcol3=y
I am trying to set the tick boxes so that, if I tick one in an ID range, all the rest tick on as well. ie if I tick record 1.0 English tick_col_2, then the French and spanish will be ticked as well.
Any suggestions will be greatly appreciated.
Andy
View 1 Replies
View Related
Aug 23, 2006
I currently have a table with contact data set out like so (first few columns shown only, but about 10 in total);
ID Establishment Surname FirstName etc1 etc2 etc3
1 ABC Frost Jim data. data. data.
2 ABC Jennings Paul data.. data.. data..
3 XYZ Smith Dave data.. data.. data..
etc...
You will see that the first two contacts are from the same establishment, but the third is not.
I would like the table to be formated so that I have a single row for an establishment, but multiple contacts in the same row, i.e. as below.
ID Establishment Surname FirstName etc1 etc2 etc3 Surname_02 FristName_02 etc1_02 etc..
1 ABC Frost Jim data. data. data. Jennings Paul data.. data.
There are 16,000 records which I need to manipulate (roughly 3 contacts per establishment), hence I wondered if there was a was to do this, or maybe export to excel first, then manipulate and then re-import into an Access table.
I am very new to access and any help would be greatly appreciated.
Cheers,
View 1 Replies
View Related
Nov 18, 2013
I know that i was discussed a lot but now it's about accexx2010.
I need to have more than 8 items on my switchboard but can't find any code for that in VBA.
I'd rather not use the option of building a regular form as switchboard.
View 14 Replies
View Related
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
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
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
Dec 30, 2014
I would like to filter data from a table using a query (from an data input form). The objective is to output all results if input form field is empty and to output results higher or equal to the type in the field if field is not Null. The query code is as follows:
IIf(IsNull([Forms]![Form2]![MaxDiffInput]);[Maximum operational pressure (bar)];[Maximum operational pressure (bar)]>=[Forms]![Form2]![MaxDiffInput])
However, is not providing any result when the input field (MaxDiffInput) as a value.
View 5 Replies
View Related
Aug 10, 2012
I want a Text Box Query on my form to display the Status, Workshop, Time, Enrolled and Limit. The problem is these values come from two different tables and the Enrolled value comes from a single field that contains the different workshops.
What I mean is: In Table[Attendees] a row contains a customer's Number, First Name, Last Name, Workshop and Phone Number. The workshops vary for each customer so one row on the table could have John Doe attending Cover Letter Writing and the next row could have John Smith attending Resume Writing. What I want is to be able to count the different workshops within the Field[Workshop] and total them and then display the total in a Text Box Query. I have a Text Box Query set up displaying Status, Workshop, Time and Limit as these values all come from Table[Workshops].
So basically I need to Query to also display a result that is the Total for each workshop from Table[Attendees] and display the total for each workshop in a Query with data from Table[Workshops].
Here is a link to an Example Database [URL] ....
I'm trying to avoid putting things on different reports and the like because the people using this are basically computer illiterate and if they have to click a button (no matter how well labeled) they won't do it and the information might as well not exist.
And if there's a better way to do it, I'm all ears. The only thing is, I have to update these workshops month by month. Since they are dynamic, I want to avoid creating separate tables for each workshop.
View 14 Replies
View Related
Dec 17, 2012
I have a table and a simple query that pulls results from the table. Nothing too crazy. But, if I were to go in and change some of the data/values in the query results it will change the respective data in the table. I know that this cannot be right. What do I have to do to either prevent the ability to change query results and/or prevent any changes in the query from altering the original data in the table.
View 2 Replies
View Related
Nov 20, 2013
I am wondering if there is a quicker way to export a query to excel then have the data in that query removed from the original table. (effectively cutting the data from the table and exporting to excel)
I understand that this can be done by exporting the query to excel then running the same query as a delete query to remove the data but I just wondered if this is the most efficient way.
I have experience of VB in excel but currently only use the basic macro builder in Access though if Access VB is more efficient I can easily learn.
View 5 Replies
View Related
Aug 12, 2014
I have a database which among other things records how jobs are received i.e.: Telephone, Email, Mail, Facsimile or Web.For each client I want to identify the percentages of each method of receipt against the total of jobs received and during different time periods.I have created a make table query for all jobs received between variable dates for a client entry of the name of the client and the start and finish dates are required to run the query.
I have a crosstab query set up to count each method of receipt and a final query to work out the percentages using the total from the crosstab query fields divided by the total of all methods.I have a macro set up to replace the table with new data when I want the stats for a different client between new dates, therefore the different methods of receipt may vary for the less active clients i.e.: they may only have telephone and email .
My problem is if I choose a client where we have not received a job by a particular method (say web or facsimile), the last query working out the percentages has fixed names to cover each method but naturally produces an error when it cannot find a corresponding method of receipt. I have experimented with NZ() without success.My question is can I either have preset standard names of the column field in a crosstab query? Alternatively in the query calculating the percentages, can I include code to ignore a non-existent field in the crosstab query.
View 9 Replies
View Related
Nov 28, 2006
I am creating a database for a hyperthetical car hire company.
I have a field with a lookup/query. The data that this query searchs for is entered into my table/form already. Is there anyway of avoiding repeating this data twice?
Iain
View 2 Replies
View Related
Apr 1, 2008
is it possible to repeat a query on data returned by the same query while returning it to a single table?
i have a query that runs on the following info:
PartNumber - the part number of an item
AsmNumber - the part number of the item the part belongs to
IsAsm - true/false on whether or not the part is an assembly
(see attached database)
the current query, when 11135 is used as input, returns 11124, 11165, 11103, and 11155. likewise when 11165 is entered the query returns 11109, 11110, and 11111.
i would like the query to return 11124, 11165, 11109, 11110, 11111, 11103, and 11155 when 11135 is entered.
the sample database attached is stripped down but accurately reflects what i'm working with. i do not know how many sub-assemblies belong to a top-assembly (could be zero, one, or sixteen), therefore i think the 'sub-query' would use the 'IsAsm' value as some sort of criteria - yes?
if what i'm asking is impossible, i'm very open to suggestions as long as they work with the data given.
i appreciate any help (especially being this is my first post) and apologize for the verbose post, i'm not good with brevity.
View 4 Replies
View Related
Mar 29, 2007
Hi there
my current query
INSERT INTO [Transaction] ( booking_ref, transaction_type, transaction_description, transaction_amount, [date/time] )
VALUES (10, 'Booking', 'Hotel Room Booking Charge', 4.5, Now());
now instead of 4.5 for transaction amount, i want to get the data from another query. My other query gives off one row and 1 column which is called "price per night" The other query name is Booking Price per Night (it uses the same [Booking Ref] Parameter.
I hope someone can point me in the right direction
thanks in advance
View 1 Replies
View Related
Mar 4, 2008
how can i msgbox data from a sql query
Tasklist.RowSource = "SELECT [Tasks].Staff_Name,
WHERE [Tasks].staff_name = "josh"
[Tasklist].Requery
i tryed to use
msgbox Tasklist.Staff_Name
but i get an error message
any ideas
View 1 Replies
View Related
Jun 20, 2005
Hey,
Can someone pls tell me how can i get data from query? The problem that i have is that i build a query that have a one row resoult. And now i would like to update some fields in my form to the resoult of the query. So lets sey that i have a Field1 to Field5 in my Query and the same way on my form. Now i would like to build a code that will get the data form Query Field1 to Form Field1? I know how to get data from one form to another but i do not know how to get it from a query? So can someone pls tell me that?
THX
View 1 Replies
View Related
Jan 21, 2007
Please help me.
I want to export a query from a MS Access 2003 to textfile even if the query has no data..
But right now, I can export it to textfile if there is data. But when the query has no data, nothing is exported to textfile.
Can someone teach me how to do it?
Thanks
View 4 Replies
View Related
Sep 22, 2005
I have two field columns
For example:
Field 1 Field 2
-------- --------
Blue Blue
Red Red
Yellow Black
I need to write a select query that will output any rows where field 1 and field do not match.
How can I write this? does anyone have any suggestions?
Thanks
View 1 Replies
View Related
Sep 14, 2006
Hi Folks
I have a form that is based on a query.
I want to change the unique ID that is displayed on this form.
The new ID is sourced from a different query. I.E. Not the same query that the form derives its information.
I have set the data source property of the field (Combo1) to the "other" query.
How do I get the Combo1 to update to the value of the first record?
Currently its defaulting to Null.
I have tried using :
Me.Combo1.Requery - This does NOTHING to the vaue.
Me.Combo1.MoveNext - I keep getting a "Method or data members not found" error.
View 7 Replies
View Related
Nov 28, 2005
Hi.
I have a table that has 1 field called IP
it containes IP addresses as data like this.
IP
255.254.253.252
255.251.250.0
Is it possible to have a query that would break this up so it becomes a new table with 4 fields like this:
IP1 IP2 IP3 IP4
255 254 253 252
255 251 250 0
I'm a beginer in this and I could not find a way so for to break this apart correctly.
Please help.
View 3 Replies
View Related
