See attached image. I am looking for ways to make my query run faster. When I run this query for small groups which will return less than 100 records, the query takes about 30 seconds. However I have some groups that have over 1000 records and that will take 3-5 minutes to run. Actually sometimes the query even bombs after 30 seconds on the large groups.
This query hits against a linked SQL database. Three of the fields gets the criteria from a form combo box. The thing that makes this query take so long is the Count. It counts the number of times the Signature Type "duedte" has been changed.
I am looking for any suggestions for how to speed this up. I want real time results so I was not sure if copying the data locally each time then running the queries would help any in the overall time.
I am opening a query in Access which has two linked sql tables but it takes to long to open. Can I choose a method (ADO) opening that query? If not, is there a way to speed up the process?
Hi I have some drop downs on my form, which has some code that decides which recordsource to use. When I first select an option from the drop down, the query takes 10 seconds to load data. I then select another option, which runs a different query, which takes x seconds. If I then select the original option from the dropdown, the data loads onto the form in less than a second.
Why is there a delay the first time round and not the next? Has Access saved some kind of query plan? If so, why doesn't it save it permanently?
hi i have a form with 12 checkbox and 1 combobox i use the following code
Option Compare Database Private Sub Form_Current() If Me.Image1 = -1 Then Me.Parent.Image1.Visible = True Me.Image1_text.FontBold = True Else Me.Parent.Image1.Visible = False Me.Image1_text.FontBold = False End If
If Me.Image2 = -1 Then Me.Parent.Image2.Visible = True Me.Image2_text.FontBold = True Else Me.Parent.Image2.Visible = False Me.Image2_text.FontBold = False End If If Me.Image3 = -1 Then Me.Parent.Image3.Visible = True Me.Image3_text.FontBold = True Else Me.Parent.Image3.Visible = False Me.Image3_text.FontBold = False End If If Me.Image4 = -1 Then Me.Parent.Image4.Visible = True Me.Image4_text.FontBold = True Else Me.Parent.Image4.Visible = False Me.Image4_text.FontBold = False End If If Me.Image5 = -1 Then Me.Parent.Image5.Visible = True Me.Image5_text.FontBold = True Else Me.Parent.Image5.Visible = False Me.Image5_text.FontBold = False End If
If Me.Image6 = -1 Then Me.Parent.Image6.Visible = True Me.Image6_text.FontBold = True Else Me.Parent.Image6.Visible = False Me.Image6_text.FontBold = False End If
If Me.Image7 = -1 Then Me.Parent.Image7.Visible = True Me.Image7_text.FontBold = True Else Me.Parent.Image7.Visible = False Me.Image7_text.FontBold = False End If
If Me.Image8 = -1 Then Me.Parent.Image8.Visible = True Me.Image8_text.FontBold = True Else Me.Parent.Image8.Visible = False Me.Image8_text.FontBold = False End If
If Me.Image9 = -1 Then Me.Parent.Image9.Visible = True Me.Image9_text.FontBold = True Else Me.Parent.Image9.Visible = False Me.Image9_text.FontBold = False End If
If Me.Image10 = -1 Then Me.Parent.Image10.Visible = True Me.Image10_text.FontBold = True Else Me.Parent.Image10.Visible = False Me.Image10_text.FontBold = False End If
If Me.Image11 = -1 Then Me.Parent.Image11.Visible = True Me.Image11_text.FontBold = True Else Me.Parent.Image11.Visible = False Me.Image11_text.FontBold = False End If
If Me.ComboBox1 = "No" Then Me.Parent.ComboBox1.Visible = False Me.ComboBox1_text.FontBold = False Else Me.Parent.ComboBox1.Visible = True Me.ComboBox1_text.FontBold = True End If
If Me.Image12 = -1 Then Me.Parent.Image12.Visible = True Me.Image12_text.FontBold = True Else Me.Parent.Image12.Visible = False Me.Image12_text.FontBold = False End If End Sub Private Sub Image1_AfterUpdate() If Me.Image1 = -1 Then Me.Parent.Image1.Visible = True Me.Image1_text.FontBold = True Else Me.Parent.Image1.Visible = False Me.Image1_text.FontBold = False End If End Sub Private Sub Image2_AfterUpdate() If Me.Image2 = -1 Then Me.Parent.Image2.Visible = True Me.Image2_text.FontBold = True Else Me.Parent.Image2.Visible = False Me.Image2_text.FontBold = False End If End Sub Private Sub Image3_AfterUpdate() If Me.Image3 = -1 Then Me.Parent.Image3.Visible = True Me.Image3_text.FontBold = True Else Me.Parent.Image3.Visible = False Me.Image3_text.FontBold = False End If End Sub Private Sub Image4_AfterUpdate() If Me.Image4 = -1 Then Me.Parent.Image4.Visible = True Me.Image4_text.FontBold = True Else Me.Parent.Image4.Visible = False Me.Image4_text.FontBold = False End If End Sub Private Sub Image5_AfterUpdate() If Me.Image5 = -1 Then Me.Parent.Image5.Visible = True Me.Image5_text.FontBold = True Else Me.Parent.Image5.Visible = False Me.Image5_text.FontBold = False End If End Sub Private Sub Image12_AfterUpdate() If Me.Image12 = -1 Then Me.Parent.Image12.Visible = True Me.Image12_text.FontBold = True Else Me.Parent.Image12.Visible = False Me.Image12_text.FontBold = False End If End Sub Private Sub Image6_AfterUpdate() If Me.Image6 = -1 Then Me.Parent.Image6.Visible = True Me.Image6_text.FontBold = True Else Me.Parent.Image6.Visible = False Me.Image6_text.FontBold = False End If End Sub Private Sub Image7_AfterUpdate() If Me.Image7 = -1 Then Me.Parent.Image7.Visible = True Me.Image7_text.FontBold = True Else Me.Parent.Image7.Visible = False Me.Image7_text.FontBold = False End If End Sub Private Sub ComboBox1_AfterUpdate() If Me.ComboBox1 = "No" Then Me.Parent.ComboBox1.Visible = False Me.ComboBox1_text.FontBold = False Else Me.Parent.ComboBox1.Visible = True Me.ComboBox1_text.FontBold = True End If End Sub Private Sub Image8_AfterUpdate() If Me.Image8 = -1 Then Me.Parent.Image8.Visible = True Me.Image8_text.FontBold = True Else Me.Parent.Image8.Visible = False Me.Image8_text.FontBold = False End If End Sub Private Sub Image9_AfterUpdate() If Me.Image9 = -1 Then Me.Parent.Image9.Visible = True Me.Image9_text.FontBold = True Else Me.Parent.Image9.Visible = False Me.Image9_text.FontBold = False End If End Sub Private Sub Image10_AfterUpdate() If Me.Image10 = -1 Then Me.Parent.Image10.Visible = True Me.Image10_text.FontBold = True Else Me.Parent.Image10.Visible = False Me.Image10_text.FontBold = False End If End Sub Private Sub Image11_AfterUpdate() If Me.Image11 = -1 Then Me.Parent.Image11.Visible = True Me.Image11_text.FontBold = True Else Me.Parent.Image11.Visible = False Me.Image11_text.FontBold = False End If End Sub
how to optimize my code to run faster???
the images are 1 KB size , 16x16 pixels and .bmp All of them are insert inside db.
i think images are too small to slow down my code. Can i write the code in a different way?
I revised my DB thinking that one big form divided by Page Tabs would speed things up. Wrong. It slowed everything down to a crawl. I found the more I broke up Forms into sub-forms the faster things went.
Incidentally this may be the answer to the problem I posted about Permission Lockups. (See Access 2000 Permission lockup posted on 2/20/08). I noticed that the big form version requested to save forms that I think I had not even opened. So when I encountered the problem on installing the DB on my network, I just did two or three saves just for luck. This seemed to do the trick. This suggested that my Permission Lockup may have been due to not all Forms had been saved on closing.
The evidence is circumstantial but I have had no trouble since.
Im new in using MS Access. How i can "tick" the yes/no button faster. I have almost 3,000 boxes to tick and its making my wrist ache... Unlike in excel where i can just drag it to the last cell....
My report has in each row at least 1 picture which is about 1.5" square when open; however, when trying to scroll up or down the pictures dazzles on and off while the pausing the scroll which moves slowly. If the pictures are removed the scrolling movement is normal.
How can this report scrolling be made more efficient?
Code: SELECT Sum(Tab1.Inputs) AS SumOfInputs, Sum(Tab1.ValInp) AS SumOfValInp, Sum(Tab1.Outputs) AS SumOfOutputs, Sum(Tab1.ValOut) AS SumOfValOut, Products.Product, Products.VAT, Products.UM FROM Tab1 INNER JOIN Produse ON Tab1.ProductID = Products.ProductID GROUP BY Products.Product, Products.VAT, Product.UM, Tab1.ProductID;
Second query :
Code: SELECT Nz([SumOfInputs],0)-Nz([SumOfOutputs],0) AS Stoc, Nz([SumOfValInp],0)-Nz([SumOfValOut],0) AS ValStoc, IIf([Stoc]=0,0,([ValStoc]/[Stoc])) AS CMP, [Sum Products].Product, [Sum Products].SumOfInputs, [Sum Products].SumOfOutputs, [Sum Products].SumOfValInp, [Sum Products].SumOfValOut, [Sum Products].VAT, [Sum Products].UM FROM [Sum Products] GROUP BY [Sum Products].Product, [Sum Products].SumOfInputs, [Sum Products].SumOfOutputs, [Sum Products].SumOfValInp, [Sum Products].SumOfValOut, [Sum Products].VAT, [Sum Products].UM HAVING (((Nz([SumOfInputs],0)-Nz([SumOfOutputs],0))>0.09 Or (Nz([SumOfInputs],0)-Nz([SumOfOutputs],0))<-0.09));
I need to combine those two query sql code to make only one query.
I have a little problem with making a query and would need your help.
I have the following temporary table that gets filled automatically with 2 records every day:
DATE | TIME | NAME | CODE
The first record will have the DATE, TIME, NAME ( always the same ) and the CODE that can be START or STOP.
What i need, is to put the 2 records from the same day in a single row to get something like that: NAME | DATE_START | TIME_START | DATE_STOP | TIME_STOP | NAME
Until now i was able to make 2 different queries. One can give me the START info's, the other one the STOP info's. My question is if there is a possibility to combine those 2 queries or to make one query to get the result i need.
I have 2 tables: first table has 4 columns: /job code/project1/project2/project3/ job code is some digits, and in next columns are the job description (different for each project).
The second table has /project name/job code/description/working hours/date/
I made the query what shows how many working hours was consumed for each project monthly and my working codes, but I would like to have job descriptions after job code, If its project1 then the description should be taken from first table from the column "project1". Is it possible to do it with query?
I have several more ids like this in this database. I am fine with the first gene abbreviation, but the second one in the parentheses is redundant. I want them eliminated however it will take too long to go through and manually delete these redundant names.
Is there a way to make a query to erase the text in parentheses and the parentheses themselves?? I think it will go as an Expression in the query or perhaps I can use an Update query, but I am not sure beyond this point.
I have a gene database with several tables. One table is called GenesMain. The other is called temp (temporary table). The GenesMain has a little more information (more fields and pulling info from other tables in the db) than the temp table (which is the basic template with basic info)
When I find new genes from places like NCBI or other genetic databases, I make a temp table and put them in there. My hope is to update the GenesMain table with this info using an append query or update query.
The first time I run the database over a new batch of genes, I am fine making a temp table and producing a GenesMain table.
The 2nd time I run the database over a newly found set of genes (like a few days later or few months later), I can easily make a temp table.
I am having trouble now updating my GenesMain table to reflect this newly found information.
Does anyone know how to update my GenesMain table with the newly made info from the temp table? Thanks
I have to make a query using information out of an existing table. In the table there are two columns which I have to use in the query. I need to have the totals of these two columns. The table looks like this: country amount1 amount2 A 2 3 B 4 6 C 5 2 D 5 3
What I want the query to look like is: country amount1 amount2 A 2 3 B 4 6 C 5 2 D 5 3 16 14
As I am dutch, my english will not be perfect Thank you in advance
:confused: I am using a Make Table Query to filter a Linked Excel Table. Is there a way to cut/drop the first 8 characters of the text out of one of the fields as it creates the new table?
Field NameExcel DataFinal Data Model_NameLATITUDE D600D600
Also, the final table has two Relationships with two other tables. When I run the Make Table Query once a week, I have to break the relationship to get it to run. Is there an easier way to dial with this?
Is there a way in a Make-Table query to tell the table to open when it's created? I would assume there is a function or SQL code that would do this, but I know very little SQL. Can anyone point me in the right direction? Thanks!
I have a table with many records each record has specific time. I would like to make query by time to see how many transaction has been done every 10 minutes.
I made a query its show me only one period. how to see all others period.
For example:
Start Time--End Time -- Amount (Total of amount for the period) 10:00:00 -- 10:10:00 -- 2000 10:10:01 --- 10:20:00 -- 1500
and for all other period :
I have attached the file with the data table and query.
I have a database named NewUpdate.mdb. I have another database named MainDatabase.mdb. Almost all the tables in NewUpdate.mdb are linked tables that are in the MainDatabase.mdb file.
I've written a simple make-table query in NewUpdate.mdb that makes a backup of a table that is located in the MainDatabase.mdb file. (See code below.)
SELECT tblProviderRate.* INTO tblProviderRateSave FROM tblProviderRate;
The only problem is that this new table is created in the NewUpdate.mdb file. I need the query to be stored in the NewUpdate.mdb file and the "new" table to be created/stored in the MainDatabase.mdb file.
This sound simple enough, but I'm drawing a blank as to what I need to do in order for this to occur. Can someone tell me what I'm leaving out??
How to make-table query to make a certain field a memo field instead of a text field? The reason I cannot use the text field is the limitation is 255 characters while I require 2000 characters?
OK, I know what records I need to delete, just not sure how to do this.
I have 2 tables. My main table, and a child table that hold some additional info for that record.
What I've been asked to do is, delete all records where in the child table the records meet a certain criteria.
So first I created a select query, added my main table, and then my child table.
Added my criteria to filter out and found the records they want removed.
So I thought I could just convert it to a delete query and be done. Nope because of the relationship between the tables it would not let me do it this way.
So the select query shows me all the ID#s that need to be removed (194k worth of them). How do I use the data from the select query, to create a delete query to remove all the files from the maintable (which should delete the data from the child table because of the relationship?)?
Here is the select query, that found the records that need to be deleted SELECT tbl_Edit_Type_IRR.Information, tbl_Edit_Type_IRR.Issue, tbl_Edit_Type_IRR.Review, tbl_Edit_Type_IRR.Main_ID, T_tbl_main.Main_ID FROM T_tbl_main INNER JOIN tbl_Edit_Type_IRR ON T_tbl_main.Main_ID = tbl_Edit_Type_IRR.Main_ID WHERE (((tbl_Edit_Type_IRR.Information) Is Null) AND ((tbl_Edit_Type_IRR.Issue) Is Null) AND ((tbl_Edit_Type_IRR.Review) Is Null));
I would like to genreate a query from a form. So when I want to run a monthly report for a month I can pick a mont or a date range and a criteria for the form such as crew meetings attended, paperwork in on time or up to seven different points of interest for a particular person.
I can do this relative simple in parameter query, but he users want a more efficient input form. Or maybe the word i "quicker". And if they use a drop down box from a data table to start the form/query, there won't be as many input errors.
Hi all, I converted one database from sqlbase to access. It is so fast in sqlbase. But so slow in access. Taking time to run the query. I need to make it fast. Any help please. :confused:
I have a query by form named QBF_Form, I use it to help users query a form without having to open the query directly. A query, Cert, is based on this form using expressions like: Like Forms!QBF_Form!Sales & "*" Or Forms!QBF_Form!Sales Is Null
This Cert query is a source for a form called Manage. I have a macro to open the "Manage" form, run the Cert query, and close the query . Everything works fine when I do this.
But when I click on the Manage form directly, I get this: Enter Parameter Value: Forms!QBF_Form!Sales
How can I prevent this from happening? How can I change the logic?
