How to search my entire database (1 Viewer)

It's been a long while since I've visited here.
I'd like to do the following, and don't know where to start.
I have looked in the sample re-usable code section, and it's possible something was looking me square in the face, but I didn't recognize what I needed.

I need to:
Search for a particular record in my customer database, based on any portion of text string that may appear in any field of any table, in my database.

Currently, when the customer double-clicks on the application icon, the main form pops up, with the various buttons for them to do what they need. All that works fine.

Ideally, they'd like that main form to include a text space, where they can enter any string of text, and either press enter, and click on a 'go' button next to it. What this would do, is pop up another form (likely based on a query), that will list all records, in all tables, based on what they entered in the text box. Then, by double-clicking on that line in the pop-up form, would open up the customer's record, from the invoice report.

Example: If they enter: CU-11111, the code would search all tables and reports, for that customer number, and pop up a form, from a query, listing all line items containing "CU-11111".

Is that too complex for Access 2000 to handle? I somehow supsect it's not, and would be grateful for a clue or two.

Thanks, Jim

try this

Someone posted this a while back. Its probably the best search tool for access I've seen. All the people who use my database love the way it narrows down the records during every key stroke.

Whom ever built it deserves the credit.

Bones

I think the example is from OldSoftBoss.

Wayne

why on earth would you want to search every field.

surely you would only search the field(s) in particular tables that are germane to the text string being searched.

in the example you quoted, I would probably search just for 11111, as the hyphen may or may not be entered.

Also, why do you think you would search reports? all you need to search are tables,

Would anyone mind breaking down this code and what it's doing? It would be great to understand more exactly what's happening on the back end to make this work.

Thanks in advance!

Cool Search Tool ?

It's just running a query against the 'main table' where 4 fields have a value containing certain text.

It could be simplified by removing the query and using sql

BlueJacket,

You might get some ideas from this material.

gemma-the-husky said:

...why on earth would you want to search every field...

...surely you would only search the field(s) in particular tables that are germane to the text string being searched...

Click to expand...

Dave's remarks are right on the money. The very fact that you need to search all Fields in all Tables has to mean that your design, vis-à-vis your data/Tables, if majorly flawed.

Linq ;0)>

missinglinq said:

Dave's remarks are right on the money. The very fact that you need to search all Fields in all Tables has to mean that your design, vis-à-vis your data/Tables, if majorly flawed.

Linq ;0)>

Click to expand...

I disagree. Not every database is composed of easily distinguished kinds of stuff (customer vs order, for example). In some databases, many different fields, in many different tables, are composed of text which may contain a keyword of interest. This is, I suppose, more relevant for an *end user* of a database than for a database *developer* or *administrator*. Another way to put it: out in the real world, people really do sometimes need to do searches on many different fields in many different tables.

-Sean

Sean_999 said:

Another way to put it: out in the real world, people really do sometimes need to do searches on many different fields in many different tables.

Click to expand...

Describe a practical example of such a search requirement and we will explain why it is really caused by inappropriate data structure.

Galaxiom said:

Describe a practical example of such a search requirement and we will explain why it is really caused by inappropriate data structure.

Click to expand...

I'm currently working on a database containing a wide variety of economic data and information about specific documents that cite these data, and the various legal and legislative actions and entities that generated and use the documents. This is all in a well-normalized data structure. There are lots of numbers, but also lots of text. Many of the text fields are longer than one word: several words is very common, and not a few contain a paragraph of text.

A given keyword of interest could be in any of dozens of string variables in about 20 different tables, and it is quite plausible that a user would have a legitimate need to find a keyword in *any* of these variables, despite the fact that the different variables contain different kinds of (text) information.

You seconded this comment by "gemma": "why on earth would you want to search every field. surely you would only search the field(s) in particular tables that are germane to the text string being searched." The problem that I and the original poster have is that for many perfectly plausible searches, there simply is not an easily identified field (or even small set of fields) to limit the search to.

Sean_999 said:

I'm currently working on a database containing a wide variety of economic data and information about specific documents that cite these data, and the various legal and legislative actions and entities that generated and use the documents.

A given keyword of interest could be in any of dozens of string variables in about 20 different tables, and it is quite plausible that a user would have a legitimate need to find a keyword in *any* of these variables, despite the fact that the different variables contain different kinds of (text) information.

Click to expand...

But they don't really contain different kinds of information if you have a need to search all of them for the same term.

You are storing attributes. Both the attribute names and the associated values should be treated as data. Your attribute names have been hard-coded in to the table metadata which is where you difficulties begin.

Instead of having twenty different tables customised for particular things you have a Thing table which holds only very basic information, ThingID and ThingTypeID. The attributes are stored in a related table with fields to hold ThingID, AttributeID and AttibuteValue.

The ThingTypeID sets the list of AttributeIDs that can be applied to the particular Thing.

Attributes are displayed in a Continuous Forms subform. See the demo I posted on this thread that shows the basics of getting records to appear on a subform without actually having them in the junction table until they have a value entered. The RecordSource query for the subform selects only the AttributeIDs that apply to the ThingType. Ordering systems can also be included to maintain the order of the Attribute display on the form.

Now your search just looks at the one Value field in a single table. Matches return the ThingID and the AttributeID so your form displays the ThingName and AttributeName of the found text. The search query can easily be narrowed to particular ThingTypeID (eg Leglistation, Finding) or AttributeIDs, (eg Author, DocumentDate).

ThingID to AttibuteID should be a many-to-many relationship. Most ThingTypes would have several Attributes that that have in common with others.

Aside from affording a simple search, this construction allows new attributes to be added to any ThingType at runtime. Moreover, a new ThingType does not need a new table and its Attributes can be assigned by the user. No need for a developer to ever add new objects.

A well designed system also stores a datatype for the Attribute value. This will also be held in the ThingTypeToAttribute junction table. It tells the system to treat numbers as numbers, dates as dates etc even though everything is stored as text. It can also store the Unit name and multiplier if applicable. Anything associated with that instance of the attribute.

A more advanced (but more complex) system stores the value in a field of the appropriate datatype and the applicable value is selected for display. This permits proper indexing of the field.

AttributeNames on the form are made to look and behave like labels by hiding their border, disabling and locking. The subform can be made to look like part of the main form by disabling its border and the extraneous stuff like selectors.

BTW Indexing documents can be done in a database server where contextual searches can be performed on a massive range of text based document types.

MS SQL server can be configured to present a file share to Windows. You simply copy your files in any structure inside that file share. MS SQL Server builds an index of the full content of the files.

The index can be queried returning the path and name of the document. The query can specify the order of the search terms and the maximum distance they can be apart in the file etc.

It is quite easy to set up.

Galaxiom said:

Describe a practical example of such a search requirement and we will explain why it is really caused by inappropriate data structure.

Click to expand...

Practical Example...Easy

Not everyone in the world or everyone who uses access knows anything about data structure, and many of these people, may have the need arise, to search every table.

My circumstance right now is I understand what data structure is and how important it is, but not much else. Where I work there is no database designer. I'm part of a small group of <10 engineers using access tables as a database for components and all their properties. This database has an administrator but only to assign rights and prevent unauthorized editing etc. He has nothing to do with the data structure. The most senior engineer chose it.
I recently began working on a design for custom machine that was built for first time 7 years ago. This machine has some very uncommon components on it. Components that went obsolete shortly after machine was built, and now it is impossible to find any info on them, online or elsewhere. The most convenient place for me to discover what these components were and their tech specs etc is our database. I already know it was entered for sure, but it is not in the table it should be. Since it is uncommon, and very unique, it could fit into a few different tables, but its not in any of them, yet I know it is in the DB somewhere, and wherever it is, it has all the info I need in it. If I can find it, quickly and easily, then I can save myself days or weeks of designer pain.

The fact is, while I have no problem agreeing that many of these problems are caused by poor data structure, the fact remains.....that poor data structure exists, and it will always exist somewhere, there will always be people using access who aren't exactly DB experts. So there will always be a need to be able to search all tables, or at least to search several tables at once.

And poor data structure is a perfect example of a practical example of needing to search. To a DB expert, it may not, but remember, anyone who needs to search all the tables, probably does not have the skill needed to change the data structure so its not needed. Which is my circumstance.

Hello Proxy .... and welcome to the site

Hopefully all of us here can help you achieve 'higher things'
However, the fact that you've explained this so well makes me think your skill level is actually higher that you are saying.

Anyway, hope to hear more from you in the near future

Proxy,

I agree that not all users and uses of Access demand a normalized data structure, but many do.
I recall an engineer colleague once commenting -- it's interesting to see how many things look like nails when the only tool you have is a hammer. I can assure you we have seen Access used as a text editor, data base, file, spreadsheet, "shoe box"...etc.

Perhaps you could give us some sample data and what exactly you are searching for, it may get you some focused options.

I agree with Colin regarding your skill level. Engineering is a discipline and I expect you know more about patterns/concepts/ generic vs specific solutions etc than most.

"shoe box" is my version of how we used to store photos--it's in the shoe box somewhere..

Good luck.

maybe you could extract a "cloud" of search tags from the documents, and search just those, rather than a full text matching approach.

Maybe even a "soundex" type value of the search tags.

I have no idea exactly how google resolves text searches for instance, but I am sure it won't be by brute force.

jdraw said:

Proxy,

I agree that not all users and uses of Access demand a normalized data structure, but many do.
I recall an engineer colleague once commenting -- it's interesting to see how many things look like nails when the only tool you have is a hammer. I can assure you we have seen Access used as a text editor, data base, file, spreadsheet, "shoe box"...etc.

Perhaps you could give us some sample data and what exactly you are searching for, it may get you some focused options.

I agree with Colin regarding your skill level. Engineering is a discipline and I expect you know more about patterns/concepts/ generic vs specific solutions etc than most.

"shoe box" is my version of how we used to store photos--it's in the shoe box somewhere..

Good luck.

Click to expand...

But I guess, Proxy's point is that the searching needs are often fuzzy. Now, while I can't think of an example where I would be looking for a string or a portion thereof in all columns of all tables in a database, there certainly are cases where cross-table searches are ok and not indicative of bad design.

A good customer of mine called me some years back and asked me how to find a name with a phone number in my database. What do you mean, I ask ? A name of a renter ? his wife ? an agent ? supplier ? superintendent ? contractor ? bank contact ? someone from the city ? He says, "Vicki told me this guy X. wants to me to call him back and it's important. I am not sure who that is, his name sounds familiar, but I can't place him. And she did not take his number down because she thought she had it when he called on her cell. But he called from a hotel front desk and he is not a guest there. Probably went there for a lunch. I told her to look into your database. Everyone's there. She told me she did not know how to do that. How does she do that ?" I tried to explain to him that she would have to go through a number of forms to find "just a name" if she did not have a context for her search. And he told me, "that's stupid ! I just want to be able to put in a search for a name and get the guy's particulars. Don't tell me, it's such a big deal! Can't you do that ?" Of course I could do that and I did that actually before his next maintenance invoice was due.

Oh, yeah, the guy turned out to be some old acquaintance who wanted to sell him some penny stock. So Vicki would not have found him in the database even with the super-search gizmo I created. But of course, that is not the point.

Best,
Jiri

Galaxiom said:

Describe a practical example of such a search requirement and we will explain why it is really caused by inappropriate data structure.

Click to expand...

Reverse engineering a shitty DB structure to get rid of all inconsistent relations in a new schema and migrate data.

Don't know HOW I missed this one, but I saw this question: Can you describe a practical example of a search across multiple fields?

Actually, ANY database created by trying to import data from a log file of some other real-world process in order to do data mining. Granted, you would not want to search 100% of the possible fields, but security searches looking for specific usernames or IP addresses would find them in at least three or four possibly different fields from imported IIS web logs and certain other security logs. I've had to do it myself more than once.

I am ALL in favor of structuring data well. I've even gotten an MVP status from this forum for doing so. BUT sometimes when you are handed a dripping sack of eel slime, you have to just dive in and SEARCH because you will NEVER have enough time to reformat it elegantly. Your boss WILL be breathing down your neck waiting with limited patience while you extract some little nuggets of value from a pile of dyspeptic dragon droppings. It's called LIFE.