I am truly stumped. I have a query that sometimes takes less than 1 second to run, and sometimes takes close to 5 minutes. I am making NO changes to the query. I first thought it was a network issue, so I moved the whole db, both front-end and back-end, offline. No change. So I thought maybe it was a linked table issue, so I moved all the tables used by the query to the front end. No change. Every once in a while, the query runs at lightning speed, less than 1 second. Most of the time, it takes 3-4 minutes, or longer.
Here's the SQL:
SELECT PCAInternalPareto1a.row, PCAInternalPareto1a.column, PCAInternalPareto1a.Noun, PCAInternalPareto1a.Rev, PCAInternalPareto1a.InspPoint, PCAInternalPareto1a.cell, PCAInternalPareto1a.DefectType, CLng(nz([firstofTruncatedOpSeq],0)) AS ZOpseq, Sum(PCAInternalPareto1a.DefectQty) AS SumOfDefectQty, PCAInternalPareto1a.poo, PCAInternalPareto1a.repcode
FROM PCAInternalPareto1a LEFT JOIN BOMOutRefDesOnly ON (PCAInternalPareto1a.RefDes = BOMOutRefDesOnly.RefDesOnly) AND (PCAInternalPareto1a.partno = BOMOutRefDesOnly.PCAItemNo)
WHERE (((nz([FirstOfComponentItemNo],0)) Like "*"))
GROUP BY PCAInternalPareto1a.row, PCAInternalPareto1a.column, PCAInternalPareto1a.Noun, PCAInternalPareto1a.Rev, PCAInternalPareto1a.InspPoint, PCAInternalPareto1a.cell, PCAInternalPareto1a.DefectType, CLng(nz([firstofTruncatedOpSeq],0)), PCAInternalPareto1a.poo, PCAInternalPareto1a.repcode;
PCAINternalPareto1a is itself a query, which always runs fast. BOMOutRefDesOnly is a table with about 84,000 records. I have indexes on the joined fields.
I know the where statement looks useless but I have my reasons for doing it this way. I tried deleting the where statement and it ran really fast. Then I re-added it, and it still ran really fast. THen slow again. I can't find any relationship between anything I've tried and the speed of the query!!!! What's going on??????
I have 3 PC's on my Network accessing a back end which is sitting on the main PC.
i.e. Main PC has Front End and Back End PC 2 has Front End and is linked to Main PC's Back End PC 3 has Front End and is linked to Main PC's Back End
PC 2 and PC 3 take about 5 minutes to load the intial main form. Once it is loaded the performance is ok. I have a Gigabyte network so know it's not just a slow network.
I have had a look at many other threads and also read http://www.granite.ab.ca/access/performancefaq.htm but nothing has helped.
Any ideas about settings in say the options page that I may have set wrong.
FYI I have Default Open mode set to shared, Default record locking set to No Locks, and Open databases using record-level locking ticked. Not sure if these are correct or if there are other settings to consider here.
I have also found that once you open the initial form once, it then loads quickly if you close it and then open it again, but if you close the database front end and re-open it it then takes 5 minutes again.
The only answer I have at the moment is to never shut the darn thing...!
Basically I have a table setup where Time Fields are stored. What I need to do is in a Query, have the difference of two Time Fields calculated, and then displayed on a Report. Here's what I have so far...
All that works fine and shows up on the report, except for the fact that the time is showed in straight minutes, so for a difference of 1:30 minutes, it just shows 90 minutes. I need to make it so that on the report in the Difference Time Text Box, it's showed as 1:30 instead of 90.
I tried putting the following in the Control Source for the Difference Time Text Box on the report, but it returns an error.
=[Difference Time] 60 & Format([Difference Time] Mod 60, ":00")
I know this is probably the dumbest thing to ask, but I am just not getting it. I'm tryin to run a Query, and I assume it's a SELECT query for Last Name, First Name, SSN. The tbl containing this information [tbl_Employees] automatically places the dashes in the SSN through an input mask. However for one type of report I will later need to generate, the SSN cannot contain the dashes. What is the criteria for this?!? I am so lost! I know it's probably something so simple that I'm just overlooking it, but I'm clueless. Can someone help me out? Thanks so much! :)
I'm running this query off a table of about 1.5mil records. If I leave the date parameter off It comes up under a minute. When I add it back, it takes 5 minutes before the query even starts running. All the fields used in filtering the query are indexed. Any ideas to speed this up?
SELECT CallsEntered.[Work Order Nbr], CallsEntered.[Date Entered], CallsEntered.[Time Entered], CallsEntered.[Primary Locator Code] AS [ASC], CallsEntered.Headend, CallsEntered.Node, CallsEntered.[Grid Id], CallsEntered.[Q Code], CallsEntered.[Problem Code 01], CallsEntered.[Primary Finding Code], CallsEntered.[Primary Solution Code], CallsEntered.[Cancel Code], CallsEntered.[Scheduled Date], CallsEntered.[Wo Status], CallsEntered.[Date CheckIn], CallsEntered.[Assigned Installer], Calendar.Week, Calendar.Year FROM Calendar INNER JOIN CallsEntered ON Calendar.Date=CallsEntered.[Date Entered] WHERE (((CallsEntered.[Date Entered]>=Forms!frmServiceCalls!txtStartDate Or Forms!frmServiceCalls!txtStartDate Is Null)=True) And ((CallsEntered.[Date Entered]<=Forms!frmServiceCalls!txtEndDate Or Forms!frmServiceCalls!txtEndDate Is Null)=True) And ((CallsEntered.Node=Forms!frmServiceCalls!txtnode Or Forms!frmServiceCalls!txtnode Is Null)=True) And ((CallsEntered.[Primary Locator Code]=Forms!frmServiceCalls!cboASC Or Forms!frmServiceCalls!cboASC Is Null)=True) And ((CallsEntered.[Q Code]=Forms!frmServiceCalls!cboQCode Or Forms!frmServiceCalls!cboQCode Is Null)=True));
were each number comes from a query field..Can I add say Migrated and Excluded within the report and possibly other calculations that I may wish to include.
I am trying to create a query that takes values from a search form and provides records. I was having issues with getting results when some boxes on the form were left blank. I found a solution to that and it worked with a small number of fields. However when I make the full form query (about 8-10 fields) and run it says the query is too complex. I wrote the sql as I could not get designer to do what I wanted. Attached is the sql that works and that which is "too complex".
Can anyone tell me if there is a way of displaying a number, which represents the number of minutes of elapsed time, into the format days:hours:minutes elsapsed?
For instance the number (in minutes) 1530 would display as = 01:01:30. 1 day, 1 hour & 30 minutes of elapsed time.
I have a table wherein the time worked by 25 employees are recorded. This table has the start time, endtime, break time and late times. The Late Time is the number of minutes that the employee is late to work. I am required to do a query of the team's (all 25 staff) late times per month. I have done a query which shows the late times of the individual on the dates that they were late to work and created a crosstab query for that.
I am going around in circles. How can I have a total of the team's late times in a query? Please, could someone please point me in the right direction?
Amongst many other things i would like to be able to see which teams in our company have access to which database. We currently use up to seven!!! different Databases which makes for tremendous fun...just kidding.
Since every Team has access to more than one Database andevery Database obviously gets used by a multitude of Teams, i thought the best way to go would be a Junction Table. Whilst building this, however, it occured to me that i could have just created fields for each Database in the Team Table which then can be ticked.
So here's my question, is it more space consuming to create a junction table, it certainly appears to be more work right now...
We have a split database with the front end on 3 user pcs and the backend on the network
I have the only full version of access on my pc for editing the front end and the others have access runtime to use the database
Problem: Since I split the database this way it takes horrendously long to make any modifications to the front end design of queries, forms or reports. I mean when I go to save changes on a form it takes about 10 mins to do it. Likewise design modifications to a form are very slow. eg click a field to move it and theres a delay of 5 seconds before the field is selected then another 5 seconds to respond to the next mouse move. Frustrating
Can anyone shed any light on the reason for this and/or suggest a solution.
btw it doesnt seem to make a difference whether the other users have the database open or not.
I have a form that takes a long time to open – around a minute and sometimes longer. The form is based on a query, and this query on its own opens almost right away. Why would a form take so long to load up? What factors can increase a form’s load time?
I have a following problem: I have a form in continuous mode. Users are supposed to filter data in it to their liking using inbuild filters in Access. After that there is a button that should calculate total weights in filtered records (at this point, I add more calculations once I have solved this issue). Code of button is as follows:
Code: Private Sub btnCalculateWeight_Click()
Dim rst As Recordset Dim weight As Long Dim material As Long Dim subtype As Long Dim locus As Long
[Code] ....
Loop goes through recordset correct amount of times but for unknown reason takes value only from the first record and sums it N times where N is number of records in recordset. Recordset seems to be correctly assigned according to messageboxes I added to pinpoint problem. PotteryWeights is a custom function I made and it works properly.
I have a form that I created that has a tab control. When the form loads, it automatically sets the focus to a field in the subform. This wouldn't be a problem, except when it does it hides the tabs at the top of the form so I have to go and scroll back up every time I click a tab so that I can see the tabs. Is there a way to adjust this? I have tried hiding a field and setting the focus to it and that didn't work. I also tried setting the focus to the actual tab, but that also did not work.
I'm experimenting with unbound object frames to embed an excel sheet in Access.If I have an excel application already open and busy, and then open my form, access freeze completely and won't do anything. My users will probably be running excel vba programs when they attempt to view this form, if there is no work around I'll have to drop the idea of embedding excel in access.
whenever an "external" thing wants to use excel (opening a file in explorer, etc), it uses the very 1st excel application open on the computer at this moment, instead of creating its own instance, or at least scanning for how busy the open application is. Because of that, I think that even loading the preview in the access form will try to use the current excel process even though it's completely busy.
This forum has been a lifesaver in the past and I hope someone can help me now.
I have a Contacts form that is used to input all the client's information. (name, date of birth, address etc).
I want to be able to force the person entering the data to not be allowed to tab to the 2nd text box (or any other text box on the page) unless in the first text box data has been entered (the first text box is client name)
However, I also need to be able to overide this if the person entering the data clicks on a "Cancel" button (another button on the page which closes the form).
Is this possible and if it is any help would be greatly appreciated!
I have time sheets from the past I need to enter but have found that some years the time punch minutes are given as if an hour equals 100 minutes not 60. Other years the minutes are given correctly. How do I format this field so that if I type 3.75 it will convert to 3.45 hours? Will I have to add another field to enter this and then convert to my field where hours and minutes are correctly entered? :confused:
I have a query that shows a list from GPS software (car fleet management), unfortunately the software is not capable of doing a decent reports thus I'm trying to use its data and do it myself.
The table structure is as follow:
Car, Date, Time, Position.
The time interval I have now is 1 minute (short time format), I don't need it so detailed, that's why I was thinking to limit this into 5 minutes interval.
Here's one that I'm having difficulty on. I know just enough about code to completely confuse myself, so if you answer this, please explain in detail. I have a training database, in which a portion of it stores the scores of events of a physical fitness test. Part of this test is a times 3 mile run. I need to calculate a point value for the submitted time, but here's my dilema...If I use the time/date feature it accepts the input as hh:mm, and will not let a time of more than 24 minutes be entered, since it sees this as 2400, or midnight in military time. I need the data to be inputed as mm:ss, and I have another text box that accepts this info and converts it to a point value. I would appreciate any help you could provide. Thanks in advance.
I have being playing with ms access but I really don't know much about it or databases in general.I have created a very simple database to gather twitter following/followers data for research purposes.One table (table01) has a field for the "boss" user (=the user who I gather data for), another field for "client" (=bosses followers or friends).Both fields are numeric and contain the users id's.In order to distinguish if the link is follower or friend there is a third field, called type which can be either 1 (=follower) or 2 (=friend).So the data would look like this:
boss - client - type 12345, 67890, 1 12345, 54321, 2
If user with user id 12345 had a follower (type 1) with user id 67890 and a friend with user id 54321...In order to avoid getting duplicate rows I also added a unique identifier which is of the form boss_id-user_id-type.So the above row looks like this:
That works just fine.For several reasons I also needed data of the form source - target.So I also made another table (table02) of this form.
67890, 12345 12345, 54321 ...
In table 2 you don't need the "type" field since the position of the user id shows the type of relationship.Still, you need a unique identifier in order to avoid duplicates, so I added on with the form: source_id-client_id..So table02 lookes like this
Both tables also have a date/time stamp for each line.As you can see, table01, having also a type field is bigger than table02.The problem is when I try to append data, exactly the same data in both tables.Appending data to table01 is ok, while appending data to table02 (which is smaller, having one less field) takes a really long time, maybe 10 times as long as appending data to table01.To make sure that no query's are causing the problem I have tried first creating temporary tables with the data to be appended, no duplicates, nothing that would cause the database to make extra calculations and used a simple update query with no filters to append data.Still I get the same result, table02 takes a very long time to finish while table01 finishes in no time.
If I have a time like 22:00 and I want to add lets say 300 minutes to it; so it now shows 03:00 how do I do this? I can do it in Excel alright with the TIME function but cannot find a similar feature in Access
I am what you would consider an extreme newcommer to Access. I used to use FileMaker as my database app. I have a bunch of data in an excel workbook that now needs to be converted to DB and I want to use Access.
I have the form set up and I have a bunch of time fields entered in minutes and seconds. They show up as 12:05:32 AM. I want this to display only as minutes and seconds (5:32) drop the hours and am. Is there a mask I can use or how would I approach this?
