Re: complex search engine
In general, a general search through multiple tables can be done. In practice, it is rare because in essence, it is a big, stinking PIG of an awkward project to program. It CAN be done but most people I know don't WANT to do so.
Here is the issue: Scope of search. The question is, WHICH information about your subject matter will be requested? You are obviously in an early stage of this project so I feel it might be appropriate to give you some basic guidelines. My favorite advice for someone in this stage of project design is embodied in two simple rules.
Old Programmer's Rule #1: If you can't do it on paper, you can't do it in Access.
This means that until you have laid out a good design, you cannot expect to write any part of your Access project. I usually advocate the creation of a design document to act as a "road map" on this journey. In it, you would identify your available data, what you intend to do with it, HOW you intend to handle it, what you can search, and what you expect as outputs. With a good road map, you can go back and LOOK at what you designed for a particular piece when it is time to implement that piece. And without a good road map, you will never be sure that you have arrived at your desired destination.
Old Programmer's Rule #2: Access won't tell you anything you didn't tell it first, or at least tell it HOW to tell you.
Remember, Access is not a subject-matter expert in YOUR subject. The Access design tools, wizards, intellisense, etc. make it an expert in database and application creation. YOU will supply the knowledge of what you want to see. So... if you WANT to see something, YOU are responsible for assuring that Access HAS that something. Stated another way, if you want to see XYZ as output, then either (a) you need to assure that XYZ is an input or (b) you need to assure that X, Y, and Z are inputs and that Access has the formula to put them together correctly.
This sometimes means working BACKWARDS through your design from EVERY OUTPUT to the corresponding input or inputs. Which means you need to have the roadmap as described in Rule #1 so you CAN work backwards.
It is the attention to details at this level and at this stage of your project that will give you the greatest payback. AND as part of applying rules #1 and #2, you will need to define what a search really means as well as what it DOESN'T mean. That will go into your roadmap so that you will know at every stage of development what you MUST do and what you don't NEED to do. The knowledge of what is NOT necessary will save you from taking a thousand avoidable steps and will help you implement faster.
In essence, that last sentence is a programming variant of "pay me now, pay me later."
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.