"Google" style search function

[QueenKirsty]

I have been asked to create a "google" style search functon for my SQL server 2000 db using and access 2003 (.adp) front end form. I would like to be able to enter something into a text box and have all the records from different tables (where that string occurs in the record) shown in a popup form, from which the user can select one of the records to go to (i.e. they will double click on the record and it will open the proper form for that table at that record).
Any suggestions? Is this even possible in access?

 

[chergh]

Sounds like the person who requested this can't actually be bothered to figure out what they actually want so has just asked for everything. When I get requests like this I just send it back to the user and ask them to be more specific about what they want. The end result is better for everyone less work for the developer and the user get less but more relevant records returned.

 

[Atomic Shrimp]

Hmmmm... my gut reaction is to say that a unified Google-style search function isn't necessarily the ideal for a database - won't the users tend to know what type of information they're searching for? - i.e. if they're looking for a customer called 'Sandy', surely their starting point needs to be a search of the customer table, not a general search (which will potentially confound them with a load of results for products that are 'sandy beige' in colour).

However, if there really is a case for this kind of search, I would implement it something like this:

-For each table to be searched, a query is created - those queries can search across multiple fields by concatenating them (only in a calculated column with criteria based on the search term). The output of each query would be uniform, something like:
Tablename
Record ID
Record name (i.e. product name, or customer name, or whatever, but all called the same thing in the query results).

-Then I'd build a union query to bring all those individual queries together

-Then I'd have a continuous form to present them, with a button in the detail section - the click event of this button can then be used to read the table name and the record ID, then launch the appropriate form to display the record.

The problem is going to be sorting those results into some kind of meaningful order - when they customer types 'sandy', how could you possibly know whether they're after customers, products, or whatever?

A better alternative might be to give them a single search form, but with separate buttons 'search customers' or 'search products', etc. They probably won't like this as much, but it makes more sense.

 

[QueenKirsty]

I understand that. Unfortunately it is not an unreasonable request. We have a very complicated database with lots of keywords and different IDs that relate to external systems so to be able to search everywhere for anything is useful. For example, if someone is searching for a particular piece of kit they might want to search for it by its location as named in the financial system, its equipment type ID from the local system, its financial system equipment type ID, its serial number, its maufacturer's type description, etc.

I am not overly worried about returning too many records. it is the number of places to be searched that is massive, not the number of records.

 

[Atomic Shrimp]

Fair enough - I'd do it as described then - a series of individual queries - each query identifying its origin, brought together in a union query, presented in a continuous form.

That way, you can just bolt on additional search queries and add them into the union, with almost no change to the interface.

Just remember that if you're concatenating fields to search them, the junction of two fields can accidentally look like your search term - for example, 'sandy' isn't found in either field1:'builders sand' or field2:'yellow bag', but it is found in the expression: field1&field2 - so when you concatenate, insert a space or other character (field1 & " " & field2)

 

[QueenKirsty]

Thanks Atomic_Shrimp. I see where you are going with this.

OK. So following the general trend of advice, a massive db-wide search mightnot be practical.

As an alternative, how can I replicate the inbuilt search that searches each form and moves to an appropriate record but instead of moving to the appropriate record, pops up a list of all the records that match?

 

[Atomic Shrimp]

Suppose you've got a table of equipment containing three fields you want to search (say, ItemName, Supplier and ItemUsage), your search query could be:

SELECT tblequipment.ID, [Equipment] AS FoundInTable
FROM tblEquipment
WHERE ((([ItemName] & " " & [Supplier ] & " " & [ItemUsage]) Like '" &[forms].[YourSearchForm]![YourSearchTextBox] & "'));

Then if you've got another table of purchases with two fields (product and supplier) you want to search, you could build a similar query:
SELECT TblPurchases.ID, [Purchases] AS FoundInTable
FROM TblPurchases
WHERE ((([Product] & " " & [Supplier]) Like '" &[forms].[YourSearchForm]![YourSearchTextBox] & "'));

Both of these queries have output in the same style, so they can be put together in a union query:

Select * from QryEquipmentSearch
Union Select * from QryPurchasesSearch

And you'll get a bunch of results that describe where the results were found, and the record ID.

Put that in a continuous form and on the button click, check the FoundInTable field, launch the appropriate form and navigate to the record identified by ID.

 

[QueenKirsty]

Thanks! You're a star!!! )

 

[HiTechCoach]

You may also find this helpful:

Universal Search on many text with details

This is a revision of the Universal Search demo that shows how to search for a list of string values in ALL non-system tables in ALL fields for the current database. To search for more than one string, enter the values in the text box in comma-delimited format. These matching records are displayed in a list box. Double click an item in the list box brings up the detail view of the first 11 fields of that table. Author: Candace Tripp

 

[QueenKirsty]

Thanks HiTechCoach! That was exactly what I was looking for. I have fudged it abit to work with an .adp rather than .mdb and now it works beautifully!

 

[saip15]

Here's another example from oldsoftboss! It works great too if you need another option.

 

[HiTechCoach]

Thanks HiTechCoach! That was exactly what I was looking for. I have fudged it abit to work with an .adp rather than .mdb and now it works beautifully!

Glad to hear it was helpful.

 

[dfenton]

Seems to me that you want to implement full-text searching in your SQL back end, and then figure out how to query that. Here's what is perhaps a starting point:

http://support.microsoft.com/kb/323739

There is nothing at all unreasonable about this request. I have a client with an Access inventory database and they have a subset of their inventory on their website, stored in a MySQL database (using MyISAM tables, not INNODB, which don't support full-text indexing, unfortunately, as MyISAM tables don't support referential integrity), and we have implemented a full-text index on the searchable fields, and it's very useful for their users.