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...
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 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));
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...!
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?
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 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.
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".
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.
I have a query that pulls information from two different columns in a linked table, and places them together in a single column in a new table. The expression that i use in the query is: Expr1: [ARTINAME]+[ TITLE]. I need to put a space between the two. Is there a way to put a space in between these two when written to the new table?
I have an enormous Post Code Table 1.7 million records, which I must update every month into usable sectors for our carrier cross refs, the UK postcode is in the format CB6 3HQ, CB6 3HR, CB6 3HS, CB6 3HT, CB6 3HW etc I need to be able to remove the space then only use the first 4 chrs ie CB63, in the above examples I know I could do a search and replace " " with "" but I need this to be run automaticly where would be the best place to do this and if coded how?
I am trying to break out a field where city, state and zip codes have been consolidated. I want to create a field where I am able to ready the consolidated right either right or left until the first blank space. For example:
"pittsfield ma 01201"
I want to read the field to break out "pittsfield"
Thanks in advance for any help I can get on this :)
OS = XP pro SP2 200GB IDE HD with 134GB free (NTFS) 200GB USB2.0 with 54GB Free (NTFS) Virtual Memory = 1gb (Let Windows Manage)
Trying to copy files from a external USB drive to the Hard drive.
I have done this a million times and now it decides to foul up. When trying to copy files from the external drive to the hard drive that are 4GB or higher I get an error "Not Enough Disk Space"
Both Drives are NTFS with Plenty of free space. Also get the same error when creating files larger than 4GB. It worked before and no OS changes have been made that I know of.
In access, I have a field in a table that I need to fix. The field contains a charge(money) value, with a code attached to it. For example:
[2345.0020680]---> The (2345.00) is the charage amount & the (20680) is the code.
[17580.04PPO]----> (17580.04) is Charge; (PPO) is the code.
**Every charge amount is different, and every codes length is different; so there is no consistency in this field.
I need to add a space, comma, backslash, or something two places after the decimal, to split the two. Once I do that I'll export to excel, and split the field into 2.
I got a massive lump of WORD documents with Avery Mail label tables -- each avery mail label template/table has a mailing address. This how these people store their data. :mad::confused::mad:
So I went through and over a couple days transfered all 458 records to an excel spreadsheet for easy importing to my access mailing database. Problem is that I forgot to clean up a couple of the fields and now I have all these values with unnecessary spaces as the first or last characters for a lot of values.
My question: Could anyone help me remove the first and/or last character of these values if the character is " "? Would it be easier to do this in excel and just re-import?
Hi! I have a field that has to be 20 Characters long. Most of the time data for this field is less than 20 characters, so the remaining has to be filled with spaces to make it 20. e.g. if there is a record xyz1234567, then the rest of the record should have 10 spaces like xyz1234567ssssssssss, meaning if i click on this record then the cursor should not blink after 7 but after the spaces. Hope I am making sense. Thanks for any input.
