Search entire DB with a keyword 2

[KColquitt]

I have a File Retention DB that needs to be easily used by all employees. I want a search function on the main page that they can type a keyword in and search the entire database. I have 5 seperate tables; I would like for the search to automatically search ALL of them and bring back all results in a sub-form, they can click on any of them to find the one record that answers their question. I see these searches everywhere-i want one like you have on here. Is this something that is possible?

 

[GKChesterton]

It's not hard. An SQL expert could probably do it with a (long) line of query code. I'm not that good, so I'd use a series of INSERT INTO queries to fill a bucket table.

[purple]If we knew what it was we were doing, it would not be called
research [blue]database development[/blue], would it? [tab]-- Albert Einstein[/purple]​

 

[KColquitt]

Any ideas on where I could find that expert?

 

[GKChesterton]

You can be the expert -- make it with some hand-holding in an hour, even if you haven't used VBA, assuming you have some aptitude. Or, if I may be improper, You can send an inquiry to themanthursday if you want to not poke through the process. That's g mail.

[purple]If we knew what it was we were doing, it would not be called
research [blue]database development[/blue], would it? [tab]-- Albert Einstein[/purple]​

 

[KColquitt]

I have 3 books here: Access VBA, Access 2007 & Access 2007 VBA for Dummies (last resort)..... I have read and read. I guess my problem is I don't know exactly what it is I'm looking for, I cant find anything that says "here is how you search your entire database". I don't have a problem working my way through it, I just don't know where to start.

 

[GKChesterton]

Oh! You're golden. Your keyword will be placed into a variable.

DoCmd.RunSQL "INSERT INTO tblBucket ... 
 WHERE this LIKE '" & keyword & "' 
    OR that LIKE '" & keyword & "'

These queries are built in pilot form through the classic Access interface. Approximate what you want as a parameter query; then switch it to "Append" query type and experiment for success. Then use the SQL code in format as above.

After your user is satisfied, you need a DELETE query to empty the bucket.

The stickiest parts will be the "polish" -- what happens when there are no results to return, when the user hits "search" and nothing is in the search field, when the user enters something with an apostrophe. Try not to think ahead to that.

Start puttering and check in when you get halted.

[purple]If we knew what it was we were doing, it would not be called
research [blue]database development[/blue], would it? [tab]-- Albert Einstein[/purple]​

 

[GKChesterton]

Here is some raw unprocessed pseudo-code. I'm sure DCount isn't done up correctly, nor the INSERT INTO hints, but hopefully shows the structure and offers some hints along the way.

Your tblBucket will need a field for "source" -- citing which table contributed the particular result. Your INSERT query will declare this value as a mere string instead of getting it from a table.

If a table has multiple fields that may contribute results, you can either make a query for each field or swing it with one query. As I said, a real SQL sorcerer can accomplish astounding things in a single pass; but I'm happy to use lots of single-use queries, and trouble-shooting is a lot easier in my experience.

If you want users to be able to click on any result record and go to that table, well, that will make for some interesting added code.

Private Sub btnSearch_Click
   Dim intResults as integer. strNeedle as String
   strNeedle = Nz(Me!boxSearch,"")  'Nz handles no-entry
   intResults = fn_QryResult(strNeedle)
   if intResults = 0 then
     MsgBox "No results found for " & strNeedle
   else
     Me!subForm.Requery  ''this form based on tblBucket
   end if
end sub
'===================================
Private Function fn_QryResult(strNeedle as String) As Integer
   Dim strNeedled as String, strSQL as String
   strNeedled = Chr(39) & Chr(42) & strNeedle & Chr(42) & Chr(39) ''Prepare for SQL - wild-cards, quotes
   DoCmd.SetWarnings False
   strSQL = "INSERT INTO tblBucket SELECT tblFirst ... " & strNeedled
   DoCmd.RunSQL
   strSQL = "INSERT INTO tblBucket SELECT tblSecond ... " & strNeedled
   DoCmd.RunSQL
   strSQL = "INSERT INTO tblBucket SELECT tblThird ... " & strNeedled
   DoCmd.RunSQL
   DoCmd.SetWarnings True
   fn_QryResult = DCount(tblBucket, key)
End Function

 

[kjv1611]

Maybe I'm wrong, but I think this can be done cleaner and easier. You don't even have to delve into the VBA code much at all...

If your tables can be joined in any way in a query, then you could (at least I'd suspect) just join all 5 tables together with OUTER joins, and pull down all the fields you need searched into the query body (query design form). Having an Outer Join is kind of like having a "no child left behind" program.

If they can't be joined at all, then surely you can still get it built.. you may have to play around with HOW it's built, though.

.............................

IF you go with a VBA route as listed in the previous posts, then you'd ALSO need to clear out that bucket table after each run, or else use a Temp table instead of a normal table. Otherwise, that database is going to get rather large rather fast!

--

"If to err is human, then I must be some kind of human!" -Me

 

[dhookom]

Could you tell us anything about your 5 tables and why a user would want to search them based on the same key word? I would expect you could use a union query of the 5 tables. A column/field in the union query could be the concatenation of all significant fields from the 5 tables (might be a 255 character limit). Create additional column to identify the source table and my a significant field or two.

Duane
Hook'D on Access
MS Access MVP

 

[KColquitt]

Thank you everyone for your assistance.

Duane-there are tables for types of files, documents that are in the files, stored files (by box #) destroyed files and a table that has the departments-12 of them. There are some people in the office that are a bit challenged when it comes to anthing related to a computer; I wanted a search that they could look up say........Tax Documents and they could put in "tax" and get a return of a list of tax documents, then they could decide if they wanted sales tax, property tax, witholding tax or whatever. The tables are set up as definitions, retention schedules (how long to keep what) and a list of box numbers in the retention area and what they contain. There is also information about what documents have reached there retention expiration and have been destroyed.

I hope that explains it better. You guys are very helpful and I appreciate your time.

 

[GKChesterton]

Six months ago I needed to "interview" a massive database. The goal was to learn its structure and workings, and there was no documentation. It had almost 200 tables comprised of 18,000 fields, and the total in records was 5.5 million.

I made a routine that, given a string, would search everything. Systematically following the few clues I had yielded a lot of information. The routine itself was exhaustive in scope and just a few hundred lines of code.

Open Database
Get all Tables 
For Each Table
   Get all Fields
   For Each Field
       If Field type is text ... and
       If Field size is not TOO Long ...
           Search for string
           If found, write to a results bucket
   Next
Next
[code]

I just realized this morning this really fits the description you opened up with -- "just search everything."   If you're interested I'll post it.  I think that it will work too slowly to be suitable.  I guess that's why we all propose solutions where you must specify what is being searched where.  

My respect for the two other recent contributors is high, but I don't agree with the suggestions.  They both recommend creating fewer, more complex queries.  This will result in fewer lines of code as well as a satisfying sense of elegance.  But I usually trade elegance for ease of understanding.  Trouble shooting SQL is tedious and frustrating.  In terms of speed, I don't presume there would be a difference.  

[center][purple]If we knew what it was we were doing, it would not be called 
[s]research[/s] [blue]da[s][/s]tabas[s][/s]e devel[s][/s]opment[/blue], would it? [tab]-- Albe[s][/s]rt Ei[s][/s]nstein[/purple][/center]

 

[dhookom]

My suggestion would first create a union query. For instance if I wanted to search the Employee, Customer, and Supplier table in Northwind, the query would be something like:

SELECT "Customers" AS TheTable, Customers.CustomerID AS TheKey, Customers.CompanyName AS TheTitle, [CompanyName] & [Contactname] & [ContactTitle] & [Address] AS SearchMe
FROM Customers
UNION ALL
SELECT "Employees",EmployeeID,LastName,Lastname & FirstName & Title & Address
FROM Employees
UNION ALL
SELECT "Suppliers",SupplierID,CompanyName,CompanyName&ContactName&ContactTitle&Address
FROM Suppliers;

You could then search the SearchMe column and be able to identify the source table, primary key, and key identifier.

Duane
Hook'D on Access
MS Access MVP

 

[GKChesterton]

Duane: What's the advantage? The advantage I can possibly see is avoiding use of a bucket table (always a plus!). Is that the advantage?

KC: You description of tables has the term "documents" only once. Are you really searching in more than one table? I'm wondering if one table needs the search and the others provide supporting information ... in which case the function is simple and doesn't require solutions as proposed. More info needed.

 

[KColquitt]

I'm not doing a very good job at explaining myself-sorry.

Files Table: File Name -Accounts Payable Records
Description - Records Documenting.......

Document Table: Title of Document - Check Vouchers
File Name - Accounts Payable Records
Date of Document - 2006
Stored Files: Box Number
Box Label Title
Acutal Location of Box(2 storage areas)
Destroyed Files: Date of Destruction (State Required)
Method of Destruction (State Required)

We are a public entity (Utility) so we have lots of silly rules that the state thinks are important.

In my vision someone could look for check vouchers for 2005 and find where they are or when they were destroyed. Does that make more sense?

 

[KColquitt]

I should have added above that I also want them to put in Check Vouchers and be able to find out what department is responsible and how long they need to be kept.

 

[dhookom]

GK,
I think the advantage would be speed, very little code, and no need to create additional tables. I believe KC could identify and combine the appropriate fields. For instance, numeric and date fields might not be part of the search.

Duane
Hook'D on Access
MS Access MVP

 

[GKChesterton]

I'm imagining the relationships ... hoping for a coherent data structure ... but you know, that massive database i cited (it was 700 tables, I had a typo error) is a total disaster in every structural aspect, which is why I needed a brute search method.

Can you clarify relationships? Here's me, imagining, and going still on the optimistic presumption that Doc is really the only target table for searching.
[tt]
Doc ------- File
(many) (1)

File ------- Box
(many) (1)

File ----- Destroyed Files
(1) (1)
[/tt]

 

[KColquitt]

That is good......we just need to add the department and the file description. When I look at your image it looks so simple, am I making this more complicated in my head than it needs to be?

 

[GKChesterton]

Make everything as simple as possible, but not simpler.
--Albert Einstein

If the diagram adds clarity, hooray! But, this indicates you'll be learning more about DB design in order to serve the end-users. Back on the "Plus" side of things, maybe our "universal search function" is going to be on the simple side as proposed in my last posts.

Your end-user puts in the word "tax". Precisely which fields in which tables could contain the word "tax"?

 

[KColquitt]

I think 1 field in each table.

Files Table-Description
Document Table- Title of Document
Stored Files-Box Label

I'm cheering for the simple side.