Queries :: Numbering Column Causing Query To Stop Responding
Aug 5, 2013
I ran some code to number old records in a column to order items in a datasheet subform however after updating the column in the inventory transactions table when opening the main form that relies on it. It runs a query to determine the customers balance due and the query for that is taking forever to run like 2-3 minutes or more however reverting to an old copy of the data it runs fine and all I did was renumber records in one column most of which had 1s in them the code to re-number the records:
Code:
Dim rst As DAO.Recordset
Dim i As Long
Dim OID As Long
Dim stringSQL as String
i = 1
[Code] .....
I continued playing with this today and the query that is breaking the 2nd level subquery that actually references the inventory transactions table that I changed runs fine but go up a level to subquery1 which references subquery2 and a sum of payments query and it chokes however sum of payments only refernces the payments table.
I did some more testing today Subquery2 and sum of payments query both run faster than what I can time with a stopwatch but when combined they take approximately 1:45 there are only 5 columns in subquery1 four from subquery2 and 1 from the sum of payments 3 of the first 4 are sum and the last is group by and then the one from sum of payments is group by
Subquery 2:
Code:
SELECT
CLng((nz([UnitsSold])*nz([UnitPrice]))*(1-nz([Discount]))*100)/100 AS [Line Total],
CLng([Line Total]*(1+nz([SalesTaxRate]))*100)/100 AS [Line Total With Tax],
[Line Total With Tax]-[Line Total] AS [Line Tax],
[Inventory Transactions].*
Code: Private Sub Form_Timer() Dim LocalTime, StartWindow, EndWindow As Date LocalTime = Format(TimeValue(Now()), "hh:mm") StartWindow = #11:46:00 AM# EndWindow = #11:48:00 AM# Dim Response
[Code] ....
The intent is to run automated back end maintenance activities while giving the user the option to delay it if they're in the middle of something.
My logic is as follows:
If it's between these two times, ask the user if the maintenance can run. If user says yes, run maintenance. If user says no shift the two times to the right and ask again in a few minutes.
As it is written, when I chose no I get an hourglass icon and if I try to do anything else Windows tells me Access isn't responding. I'm guessing that Access is hanging since I've initiated the Do Until and then stalled it by telling it to wait 3 minutes before it goes to the next step. I suspect that if I waited it out, when the new Startwindow hit I would be asked about running the maintenance again.
My issue is that I need the database to remain responsive to whatever the user needs to do while I wait to ask about running maintenance again. Therefore, I don't think Do Until is my answer - at least not the way I've implemented it.
The will ultimately be set on a 10 minute timer interval and start/end window will be 01:00 and 01:15 AM respectively. The times you see above are there for testing so I can see what happens.
[Forms]![FormName]![Field] or [Forms]![FormName]![Field] Is Null
Ordinarily this works fine in returning either the selected value or all values if left null.
I need to pull in data from a Crosstab query, which means setting my parameter labels to [Forms]![FormName]![Field].
The problem is that setting the parameter labels is conflicting with pulling all records if the form dropdown is left null.
If I keep my parameters simple and just say [Forms]![FormName]![Field] then the query works with the crosstab data, but I can't do that. I need to show any records if the dropdown is left null.
The crosstab data isn't specifically the problem but needing to set the parameter names seems to be
I think I may have found a workaround by labeling the column headings in the crosstab, which means I don't have to assign parameter labels
It would still be good to know if there's a way of making it work with the parameter labels but this will do for now...
I have a query with multiple fields that is being run off of 3 parameters (linked for selection in a form). The problem is, I wanted to enable a select all feature, so I included a "Or ... Is Null" part in my criteria section, so that when nothing is selected, the query/report returns all records.
Okay so the problem is whenever I run the query with nothing selected for the parameter and then return to design view for the query, a new field has been created in the query design, titled with the expression I use to pull the parameter value from the form. This is frustrating because then that is causing errors in another report I run that pulls values from that query.
created a query (in Access 2010) that joins several linked tables (to an Oracle database). The query runs in about 20 seconds when I filter with a hard coded date (e.g., #12/31/2014#). The Oracle table column Im filtering on is defined as date/time.
When I attempt to change the hard coded value to a soft coded value (e.g., Forms![Form1]![Latest_Extract_Date]), the query runs over 5 minutes. In this case, the form field has the exact same value (12/31/2014).
Ive encountered similar issues using Access 2000, 2003, etc. This is quite frustrating. Does Access interpret #date value# is a special way? Is there a way to trick Access into the thinking a soft coded date is a hard coded date?
I've taken on the task of transitioning excel reports (and their format) to a database. One report summarizes all the parts of a piece of furniture, and the order of the machines each part goes to.It looks similar to this:
Part# Part Name Machines/Operations M7264Top Panel 112114116120121325216 M7265Under Top Panel 112114116120121 M7266Base Assembly 411325216311310312316
The table this comes from has both operations and setups, so I used a query to filter only the operations.The query (qryOperations) result looks like this:
i need to add sequential numbering (1,2,3,4...) to each line of the qryOperations and use the sequential numbering as the column header.How do I add sequential numbers to the query, that restart after each change in part number? I can do this in a report easy enough, but not a query.This is the SQL of the query I need to have the sequential numbers in...
SELECT tblparts.[PART NUMBER], tblOperationCodes.MachineCode, tblOperationsList.Order FROM tblparts INNER JOIN (tblOperationsList INNER JOIN tblOperationCodes ON tblOperationsList.OperationCode = tblOperationCodes.OperationCode) ON tblparts.[PART NUMBER] = tblOperationsList.[PART#] WHERE (((tblOperationCodes.Function)="O")) ORDER BY tblparts.[PART NUMBER], tblOperationsList.Order;
I have a query that groups the data by customer. How can I create a "Transaction Number" field where I assign a sequential number to each invoice, starting at 1 with the oldest invoice and increasing the sequential number by 1 with each invoice. I need to create this field in a query without code.
I have a database with numerous nutrient lab values per food item and zero to 20 tests per food item; some 600 food items
I want to select the last 5 tests per food item which should be no problem using the "TOP " type statement.
After I have the "TOP 5" record I would like to create another field to number each record automatically with in the query so I can run a cross tab query to display these records 1 thru 5.
I have column called "order" in table called "mov" and this column has this layout
Code: 1 2 14 255 222 1755 12
And I want to update this column to be corrected numbering from 1 to the last cell number - lets say it 17540 - this update has no criteria conditions, just this field.
I have a form with tab set one tab called "Enter Receipt" and another that houses 2 queries called "Reconcile". My issue is when I open that form, I have an On Current Macro to go to NEW record for my Enter Receipt, but I am getting a delay while the query status bar runs the other queries. I was hoping not to have those ran until i enter the parameters and hit the run button on that "reconcile tab".
Everything else works, i just need the queries to keep from running when i load the form. my queries i moved from EDITED to NO LOCKS thinking the On Current new record may affected them, not change in delay.
I am trying to run a simple update query to copy data from one column (Addrl1)to another column (Working_Addrl1) within the same file and I can't for the life of me figure it out. Then I need to repeat for addrl2 and addrl3 to working_addrl2 and working_addrl3.
I have created a cross tab to extract pipeline and sales for Q1 2014, Q2 2014, Q3 2014 & Q4 2014... the user can select the quater from a multivalued text box...
Now for the final output, have created another query which pull the above four quarter in each column from the cross tab...now the problem arises when i change the quarter to Q2 2014, Q3 2014, Q4 2014 & Q1 2014..it gives an error "Microsoft office Access database does not recognizes "Query name" as a valid field name or expression".
The error is because the second layer of query does not identifies Q1 2014.
How do i make access change the column automatically when the Q1 changes to Q2...
I have the auto ID number set up set up on my Access database which gives me membership numbers. How do I have a supplementary auto numbering column to give me invoice numbers. You used to be able to do this automatically up until a few years ago. Now I have to enter them manually.
I am looking to add a column in a query that will give a Y or No to previous column data if it contains TEXT or NUMBER (It could read "TEXT" or "NUMBER" or even Y for text or N for number).
in this query, I have about 25 small queries, 3 fields each, each linking to this one big table where one field of each query links to one field of the big table. Whenever I run the query, my program hangs up and I'm eventually forced to force-quit it. Is it because my computer isn't strong enough (it's your typical desk top computer for an office job) or could it be because the servers in which all the access databases are stored on aren't very good networks?
I have a table with a field 'Fax number', type: text (since occasionally we write a comment in there, like 'prohibited').
Some records have fax numbers, others are empty.
I want to find all records which do have a fax number. So I wrote into the Query: "is not null", expecting to get only the records which have a fax number or some text in them.
In fact, all records came up in the query, empty as well as non-empty fax fields.
I was wondering if the 'empty field' had a blank space in them, but could not find any. Tried backspace key, but there was nothing to backspace on.
I used the find-replace utility and searched for single space in Whole Field. It picked out quite a few records, but not all - so something invisible seems to be there.
However, when I opened the 'replace' window of find/replace, and had the replace window empty, then clicked 'replace', the msg came 'Access cannot find the specified text'.
What am I doing wrong? What do I have to do to get the query to work?
I know my way around Access reasonably well but am by no means an expert. I have created a system that I use in my business for hardware/software requests, and was told to do it in Access. I did ask that we do it on SQL Server with a Web Front End, but we are where we are.
Now, I have been on a SQL query training course so I know the basics, but am a bit confused on this one since Access has been added to the mix.To make matters more frightening, this is Access 2003!
[T_Hardwarelist] ID (primary key, autonumber) Make Model Description
[T_UserHardware] AT Reference ID
[Request] is inner joined to [UserHardware] on the AT Reference column. [UserHardware] is inner joined to [Hardwarelist] on the ID column.
The select query I have is basic and does just what it says on the tin; it shows who has what hardware.The query:
Code: SELECT T_UserHardware.[AT Reference], T_UserHardware.ID, T_HardwareList.Make, T_HardwareList.Model, T_HardwareList.Type, T_HardwareList.Description FROM (T_Requests INNER JOIN T_UserHardware ON T_Requests.[AT Reference] = T_UserHardware.[AT Reference]) INNER JOIN T_HardwareList ON T_UserHardware.ID = T_HardwareList.ID WHERE (((T_UserHardware.[AT Reference])=[Forms]![F_Request]![AT Reference]));
However, when it comes to adding NEW hardware to the requestI have a form called F_Request.I have a sub form called SF_Hardware.The SF_ Hardware subform runs the aforementioned query, and shows what hardware is assigned to the parent request.If I add new hardware via a dropdown on the form, it adds it in to [T_UserHardware], but it also adds another value on the [T_Hardwarelist] table. E.g. if I add a "HP Compaq 8200 Elite" (which is stored in [T_Hardwarelist]) it adds it to the [T_UserHardware] table correctly, but creates a second (third, fourth, fifth) entry on the [T_Hardwarelist] table for the same kit.
I think its confusing because of using a select query and might have to run a 2nd query on save or something like that, I had a working version before that had the make, model etc in both tables and didn't have a 3-way relationship. It'd be nice to have the request table with the high level info, a hardwarelist table with our catalog of kit, and a userhardware table just containing the ID and Reference for the hardware/request rather than duplicating the information.
i I have two queries.. What i'm hoping is to combine the result into one query but not in one column only but instead the result of the second query should be beside the first query.. The result of the second query should be added as a new column.
First Query
SELECT tbl_uSers.UserName, Count(tbl_rEceived_eMail.EntryID) AS eMailReceived FROM tbl_rEceived_eMail INNER JOIN tbl_uSers ON tbl_rEceived_eMail.UseriD = tbl_uSers.UseriD GROUP BY tbl_uSers.UserName;
Second Query
SELECT tbl_uSers.UserName, Count(tbl_rEceived_eMail.EntryID) AS eMailProcessed FROM tbl_rEceived_eMail INNER JOIN tbl_uSers ON tbl_rEceived_eMail.UseriD = tbl_uSers.UseriD GROUP BY tbl_uSers.UserName, tbl_rEceived_eMail.ProcessedYN HAVING (((tbl_rEceived_eMail.ProcessedYN)="Y"));
I have a VERY simple select statement that I am using in a pass-through query: "SELECT * FROM dbo_vReturn;", but I cannot get this to work. Each time I run the query it generates an error: "ODBC call failed - Invalid object name "dbo_vReturn" (#208)".
The dbo_vRteturn is a view in the backend. I can open this table/view from Access using an ODBC connection but when I try to run the pass-through query it falls over!
Does anyone have any idea what is causing this?:confused:
I have a linked table with 3 significant columns in it: Marque Model Volumes (there's actually about 12, but I only need these)
and I need to create a new table summarising the contents and creating new columns at the same time Marque Model Model_name (concatenate marque and model, easy to do) Vols sorted: Descending Rank (this is the problem)
I need to 'Rank' the table so the model with the highest volumes is ranked #1 the second highest is #2 and so on. Is there a command within access to allow this to happen? in SQL-Plus from Oracle I can use the Rownum command to create the entry, but this does not have an equivilent in access. At present, I'm creating the table without the rank field, then adding it in design view, setting it for autonumber, saving, and resetting it to number. This is long-winded and frankly, messy and wrong. I shuld be able to do this in one go, but I can't. I've been using access for about 6 years solid and have not been able to resolve this 3-month-old problem and it's driving me mad.
I could not use 'Auto Numbering' for each line, as I need out some lines accumulated for one invoice number 'ICP001'.
My questions:
1. What can I do to let me Automatic generate invoice number which increment from last number? 2. What can I do to let me easy to put those 'invoice number' into my Data Entry table's invoice number field? (p/s: Append Query? Update Query?) 3. What if I need sorting (example to group ONE customer and ONE Particular) for my invoicing number? What I meant is ... let say there is 30 lines customer CUC with particular Part B in a same month, and I wanna get them into same invoice number.
I have a TV Episode database. In the database I want to number each episode of a show. How do I autonumber a show's episodes when sorted by season then episode and have the autonumber restart at 1 when it hits a new show title?
I have a report that runs off a query that is sorted in descending order the price of something. This price column is in the middle of the report. Every time I try to add a function (sum or count of a column for example) in the report footer or header however, my report is then immediately resorted in ascending values of the first column.
