Speeding Up Searches

Mar 18, 2008

My Access DB has two Number fields and five Text fields (each having field size of 200). They contain text. The DB is organised year-wise. I have now crossed 15000 records. I find that the search is very slow. Search is done on all these text fields for each word typed. Some one suggested that I index the fields to speed up the search. When I tried to change the indexes to Yes, it didnt accept. Is there any other way to speed up search?

As an alternative, I created an append query to append records of the year I select in my form to another table so that I can search that table instead of the main table which contains all years records. But the append query is not appending records.

Any solution would be appreciated

View Replies


ADVERTISEMENT

Help With Speeding Up A Query

Nov 24, 2006

Guys,

I have built a database with varoius linked tables and spreadsheets, and have written a query that accesses a linked table and spreadsheet, the query populates fields in a subform each time a different record is selected.

But this query is slowing up my database as there is a few second delay when changing records, I know it is this query that is affecting the speed as before I wrote the query the crossover between records was instantaneous.

The query only pulls forward 6 cells of data from 2 linked tables via an ODBC connection and from 1 refreshable spreadsheet with an SQL.

Any thoughts as to how I can speed my database query up?

View 1 Replies View Related

Speeding Up Performance On A Wan Network

Jan 20, 2006

I have a access application which is split into 2 bits with the backend being on the network. The thing is its very slow performance wise and i want to try and optimise it...

View 2 Replies View Related

Speeding Up Linked Tables

Nov 11, 2004

I have observed that if u refresh path of / link tables from an external database (the backend) and if the backend is currently in use by some-one else on the network (LAN in my case) it takes a lot of time compared to when no one else is using it. Is there any way to get around this?

View 5 Replies View Related

Speeding Up MS Access 2003 Form

Jan 13, 2005

I have created a form to summarize sales data for the past two years. The form uses a tab control with 12 tabs(By Region, Country, State, SalesRep, etc.). Each tab has its own subform link to its own query(Each query has two subqueries so that I can get percent difference). The subforms also have total fields at the bottom in the footer. The sales data comes out of two differnt tables so I can compare this year to last year. My problem is speed. It takes about 35 seconds for the form to open. Is there a smarter way to do this.

View 1 Replies View Related

Accounts System - Audit Trail (needs Speeding Up!)

Mar 16, 2008

Hi all,

Through years of hard work I have created a fully functioning double-entry accounts system with epos and inventory management capabilities for my retail business.

Each transaction is stored once - either in tblPurchaseInvoices, tblSalesOrders, tblPayments, tblReceipts or tblJournalEntries.

The double entry part is created automatically by queries as is all the vat/sales tax information. Producing: qryPI_VAT, qrySO_Vat, qryPaymentVat, qryReceiptVat, qryTradeDetors, qryUnpaidInvoices.

The audit trail then combines all of these queries (a total of 11!) in a union query. This is obviously a very slow process and produces 63,354 records currently. Searching for information in this list is a nightmare as are calculations!

Can anyone point me in the right direction of creating a more efficient audit trail. I would have thought one transaction table would be the way to go but I can't see how it could be done.

Thanks in advance for any help.

Cheers,

Chris

View 13 Replies View Related

Searches

Aug 3, 2006

Hello,
I am creating a database for a client and have come across a small problem.
The basic background of the database is that it will be used to record and maipulate the results of observations that are conducted on members of teaching staff.
The client wants the ability to search for people that have not had an observation or have an outstanding observation. The observations are made on a termly basis (so there are three terms. 1, 2 and 3). When an observation is entered into the database, the term number is selected from a drop-down box.
I was woundering if anybody could help me with this as I am unter the impression that I am going to need to search for records that don't yet exist and I am unaware of how to do this in a database.

Any help will be much appreciated.

Many Thanks
Jason.

View 1 Replies View Related

Boolean Searches? Possible?

Jun 21, 2005

I have a search tool, but right now it is 100% case sensitive in the sense that if i search for "test" and my entry is "Test" it will not return any results.

is there any way to setup so I could say search for "Test" and it would find "test" properly? Or even to the extreme where I searched for "Tes*" and it came back with the "test" entry.

Thanks,

View 8 Replies View Related

Item Searches

Jul 6, 2005

Hi everyone i was just wondering if anyone new how to do the following;

Data is entered into a text box named txtItemNo on exit a search is performed and the next textbox txtDescription displays the results.

There is a table with itemnumber and item description in it the results would be the description.

Any ideas? This is on a subform, dont think this amkes any difference though
Thanks in advance :)

View 2 Replies View Related

Combo Searches

May 25, 2006

Hi Guys,

I apoligies in adcance, this has probably been asked several times, but I tried searching the forum but couldn't find anything.

Just now I have a report running from a query were a name is input, the data is pulled out the table and is presented in the report.

I want to take out the potential for user error by having them select the name from the drop down and then running the search.

Years ago (I'm a little rusty) I managed to figure it out, but for the life of me I can't remember!

Any ideas?

Thanks

Steve

View 2 Replies View Related

Mutlpe Searches For One Query?

May 11, 2005

Hi, in my attached databas ei have a form. If you look at the form you can see what it does. Where it pulls records for a student, i would like another button, that when pressed, will search all records for that student, with these records it will, then scan all the dates and the attended field. if the attended field for one date is all absent it will return the number 1, if theres two date where the attended field is all absent it will send back 2 and so on... the number will be outputed to a textbox

any thoughts
Thanks
Chris Lynch

View 2 Replies View Related

Wild Card Searches

Jun 22, 2005

Ive read loads of posts about Wildcard searches but am unable to find the answer to my problem. In one of my boxes on my search form the user places in the search criteria value, this can be from A1 - A16 all the way to G16. At the moment i'm using this as my seasrch query:

[Forms]![Search]![BoxNo]

So if I put in C1 then I get all the C1's, great. If I want to wildcard then I put in Like "*" & [Forms]![Search]![BoxNo] &"*", and again it works, this time it call enything with C1 in it i.e C13.

How can I adjust my query to do both, sometimes I only want the exact match othertimes I want to wildcard :confused:

View 4 Replies View Related

Newbie To Searches Through Forms

Oct 13, 2005

I have a form with a search text field, inwhich i want the user to type in a value to the search text and then I want a query which would search two tables and return which table the results in. i.e

Form:
Search Text Field ----> ABC12345

Table 1
Date
Container Number -----> No Record of that number

Table 2
Date
Container Number ------> Number Found

Result of Search

Date
Container Number ABC12345
Table 2

Can any one advise how to do this??

View 4 Replies View Related

2 Issues With Combo Box Searches...

Oct 10, 2006

Hello, here goes...


#1

I have a form with a combo box to be used for navigating to records (created using the wizard), based on a query with these fields:
1 - Last Name (Sorted Ascending)
2 - First name (Sorted Ascending)
3 - Company
4 - CustomerID (PK)

The combo box only partially works, it pulls up a list, but here is the problem:

If the name comes up 'Hartman, BillyBob', and there is only one Hartman, then all is well.

But if you have several Hartmans, say:

Hartman, Ace
Hartman, Bob
Hartman, Stan

and say you try to select Bob, it always goes to the first Hartman in the list (in this case Ace).

It is a pain in the ace, what am I doing wrong? Thanks for any help...

(I will ask #2 after this is resolved)

View 14 Replies View Related

Disable Wildcard Searches

Jan 30, 2008

Code:If Me.Search_Criteria_textbox = "*" ThenMsgBox "Please enter a full part number" & vbCrLf & "Wild cards are disabled", vbCriticalGoTo Exit_cmdSearch_ClickEnd If

This is the code I am using to stop * wildcard searches and it works fine. My problem is when users enter partial number enclosed like this *1345*.

Is there a way like above to stop them from entering that?

Thx!

View 2 Replies View Related

Forms, Queries And Reports (Searches)

Jul 16, 2007

HI folks

I have a db. I have created a search form which when you click the search button executes a query to find the required records.
The query is currently outputting to printpreview of the query.

What i want to be able to do is have the option to select the correct record and then print it. The search can either be exact and return 1 record or use a wildcard and return a few records.

I have the report setup and working I just can't for the life of me figure out how to link everything.

Maybe I've missed something I don't know.

View 3 Replies View Related

Forms :: Limit Searches To One Text Box

Sep 19, 2013

I have a db with around 400 records. It contains user's basic information such as first name, last name, supervisor...

Problem is that one supervisor can be multiple users supervisors so they are showing up a lot each time I use the search tool. Is there a way to disable searches on the supervisor field?

View 1 Replies View Related

Adding Multiple Searches In Where Condition

Oct 16, 2013

I currently have this for my where condition, and searches the form based on the clientID selected in the combo box.

The catch now is i have to add another search.

="[ClientID] =" & [TempVars]![ActiveControlValue]

now i have to also search [ProductID]

The combo Box is set up like this:

ClientID....ContactName....ProductName....ProductI D
Hidden.....Visible..............Visible........... ..Hidden

How can i add [ProductID] into the existing where condition to add an additional parameter?

View 4 Replies View Related

Form That Searches Query With 30+ Linked Xls Files

May 4, 2006

I've been looking around and have found some posts that pointed me in the right direction, I just can't get it to work. What I have is 37 excel files of competitor cross references. There are 2 columns in each excel file, our number and their number. I have inserted them as a linked table in the db. What I want to do is create a form that has a field for every part number and make all of those fields a search field. That way they can type in any number and get all numbers back. I have created a query but once I get past 4 linked excel sheets then I get errors about a type mismatch in expression. Also I can't get the form fields referencing the query to show up when I open it. If I leave the query at 2 or 3 fields and use [Forms]![CrossRef]![txtItemNumber] in the criteria of our number in the query, it works. I get a window that pops up when I just open the query asking for a number, I type it in and the query returns the number and competitor numbers. Am I doing this the hard way?? Thanks for any ideas...

View 1 Replies View Related

General :: Database That Searches Values Between Two Columns

Jun 26, 2012

I'm new to Access programming. I want to setup an Archiving/Storage Database which contains data of boxes with folders in it. The data would be the box number, begining folder, ending folder and box description (See Below).

[BoxNum] [FromRange] [ToRange] [BoxInfo]
123456 10000 10100 Case File
123457 10101 10954 Batch File
123458 11234 11545 Case File

I would like to setup a database that once the data is entered a user can open a form and search for a folder like 10054 by entering it in to a field like [SearchBox] and search the values between [FromRange] and [ToRange] and retrieve that boxes information 123456.

View 7 Replies View Related

Queries :: Wild Card Searches With Brackets

Jun 26, 2014

I want to prompt the user to enter a merchant name, but want the results to return close matches.I know how to use the wildcard in the Criteria field of the query, but I want to use brackets.I know that "*Southwest*" Will return Southwest Airlines.So I tried *[Southwest Airlines]* and it treats the criteria like a text string.

View 1 Replies View Related

Drop-Down Boxes And Multi-Field Searches

Jan 12, 2015

I am designing a search query that will allow the user to look up a record in a database to view it. I have everything already set up, and most of it working properly.

The user can recall a database entry using 7 different criteria--Type, Customer, PartNo, JobNo, Warehouse, Bin, and Shelf. The Type and Warehouse entries on the database are drop-down values, the other 5 values are text entries.

So far, I have been able to get the look up query to pull up the desired records on the Customer, PartNo, JobNo, Bin, and Shelf criteria using

Code:
Like "*" & [Forms]![Search Form]![Customer] & "*"
.

However, with Type and Warehouse criterion, the two that use drop-down boxes in the database, I have been unsuccessful in being able to call up any records using either the above partial or the more exact:

Code:
=[Forms]![Search Form]![Type]

I did try to change Type to a textbox on the look up query, but that was similarly unsuccessful. On a side note, I must use drop-downs on the Warehouse field since I have another query that concatenates that value with a couple others.

How can I, without delving into VB coding unless absolutely necessary, format the lookup query so that it will read the values of the drop downs?

View 7 Replies View Related

How To Enable Users To Use Wildcards In Their Searches On A Form

Jul 22, 2013

I have a Excel spreadsheet with an embedded query that pulls from an Access table. The users use a spreadsheet with an embedded query to search on a field called "Circuit ID", to bring up results showing the history of a given circuit id. Currently they have to enter an exact match, for example if the circuit id value is DHECHUIOY, they need to enter this exact value DHECHUIOY. They want instead to be able to enter DHEC and a wildcard character to bring back every value that begins with DHEC. There doesn't seem to be a way to do this in a spreadsheet with an embedded query.

I finally decided that it would be easier to create a form in Access

How I can create a form which allows users to search on a value using wildcard characters ? (the user enters the wildcard character).

I'm on Access 2012, Windows Vista

View 1 Replies View Related

Make A Querie That Searches For Other Queries That Contain A Certian Word

May 10, 2007

ok, i wanna make a query that will display the information from other queries containing the word "complete" in their title. All of these queries have the same columns names to them so they should fit together nicely but if someone adds a new query in the future with the word "complete" in the title i want this query to pick it up. Is this possible?

View 9 Replies View Related

How Can I Create A Form Where You Can Enter Information And It Searches The Database.

Nov 11, 2004

Hello,

Does anyone know how I can create a form which asks for the name or lastname of the customer and does a search throuout the database and brings up all that customers informtation. And if their is more than one thats are the same you can switch through them untill you find the right one.

Thank you

View 2 Replies View Related

Queries :: Performing Keyword Searches Of Text Field

Nov 16, 2014

I am trying to perform some keyword searches of a text field. My preferred option would be to hold all the keywords in a separate table and then use something like

SELECT * FROM Text Table, Keywords Table WHERE (((InStr[Text Table].[Text],[Keywords Table].[keywords])) > 0 ));

This works and returns anything which has the string from the keyword table, however I wanted to look for whole words only. I can do this in SQL using a regular expression along the lines of [!a-z] keyword [!a-z] which only finds the string where it has no letters directly either side of it.

What I would like to do is combine the two methods so I hold my keywords in a table and then use them with the reg ex to find whole words only.

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved