Keyword search across multiple tables

[TJEngel via AccessMonster.com]

Hi everybody,

I am an Access newbie. I THINK this can be done.

I would like to have a search box on a form where a user can enter text and
click a "Search" button. Clicking the search button will a) run a multiple
table query (on a single field in each table) and b) display the results of
this query in a report for the user to view. I basically need to conduct a
multi-table query with the criteria equal to the text that the user enters in
the text box, and display the results in a report.

Is this do-able? Or am I delusional?

Thanks, Monsters!

TJ

 

[Gina Whipp]

TJEngel,

Have a look at http://allenbrowne.com/ser-62.html

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Lars Brownies]

TJEngel,

To search across multiple tables you can use a union query which can be
incorporated in Allen Browne's solution Gina mentioned. A union query
consists of multiple queries of which the order of the fields as well as the
number of fields must be identical. For more info see:
http://www.databasedev.co.uk/union_query.html

Lars

 

[John W. Vinson]

Hi everybody,

I am an Access newbie. I THINK this can be done.

I would like to have a search box on a form where a user can enter text and
click a "Search" button. Clicking the search button will a) run a multiple
table query (on a single field in each table) and b) display the results of
this query in a report for the user to view. I basically need to conduct a
multi-table query with the criteria equal to the text that the user enters in
the text box, and display the results in a report.

Is this do-able? Or am I delusional?

Thanks, Monsters!

TJ

The UNION query suggestion will work... but the need to do this suggests that
you have multiple tables containing "the same" field. This is probably *not* a
properly normalized database design! In a good database, each kind of
information will be stored in one field, in one table; you'll link to that
table from other tables.

What are all these tables? Are you perhaps making the very common mistake of
having a different table for each month, or for each department, or for each
<something else>?

 

[TJEngel via AccessMonster.com]

Thanks for all of the suggestions, folks! I am beginning to work through
this and have made great progress.

John, I rethought my strategy and now see the advantage of consolidating
tables. As a result, the union query is no longer necessary.

Making Access baby steps is so rewarding! Thanks again!

-TJ

Hi everybody,

[quoted text clipped - 12 lines]

TJ

The UNION query suggestion will work... but the need to do this suggests that
you have multiple tables containing "the same" field. This is probably *not* a
properly normalized database design! In a good database, each kind of
information will be stored in one field, in one table; you'll link to that
table from other tables.

What are all these tables? Are you perhaps making the very common mistake of
having a different table for each month, or for each department, or for each
<something else>?

 

[Lars Brownies]

The UNION query suggestion will work... but the need to do this suggests

that
you have multiple tables containing "the same" field.

I once built such a search routine. It was a global search over specific
memo fields of different entities.

Lars

 

[John W. Vinson]

I once built such a search routine. It was a global search over specific
memo fields of different entities.

I've done something similar in another "special case" - my concern is that it
can also come from a very typical beginner's design mistake. Of course it's
okay if you know what you're doing!

 

[TJEngel via AccessMonster.com]

Looks like I spoke too soon!

John, I have re-visited my design, and it seems that I really do need to keep
these tables distinct and use the union query to conduct a search.

So far, I have things working out just fine. I have implemented and tested
out the search interface at http://allenbrowne.com/ser-62.html (thanks Gina!),
have built a union query across tables (thanks Lars!). The only kink now is
incorporating the two. I think I understand the theories at work, but I may
be lacking the skills with code.

Issue: The union queries I build are unrelated objects. When I use the code
from Allen Browne on my "AllFacts" search form, I am having trouble getting
the text box that would display search results have a Control Source as the
field from the union query. Am I being clear on this description? Whenever
I attempt to enter the field from the union query as the Control Source for
that text box, I get "#Name?"

The name of my union query is "UNION TEST."
The name of the field in the union query that I need is
"IncomeGet_CrisisAssistanceServices."

Hope I've described this correctly. Thanks for all of the help!

TJ

 

[Gina Whipp]

I am not sure from your description *exactly* what issue you have, so let's
deal with the #NAME? issue and see if that clears up your problem...

Here are some reasons for the #NAME?, check to make sure none of these apply
to your situation...

1. Make sure that the field specified in the control's ControlSource
property hasn't been removed from the underlying table or record source.

2. Check the spelling of the field name in the control's ControlSource
property.

3. If you specified an expression in the control's ControlSource property,
make sure that there is an equal sign preceding the expression.

4. Make sure the names of the field does not match the name of any of the
controls in your formula

5. Check for missing references

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[TJEngel via AccessMonster.com]

Got it!

I built a simple query based on UNION TEST, then appended this simple query
into a table that is related to my search form. Keyword searching across
tables is working!

Huzzah!

TJ

Looks like I spoke too soon!

John, I have re-visited my design, and it seems that I really do need to keep
these tables distinct and use the union query to conduct a search.

So far, I have things working out just fine. I have implemented and tested
out the search interface at http://allenbrowne.com/ser-62.html (thanks Gina!),
have built a union query across tables (thanks Lars!). The only kink now is
incorporating the two. I think I understand the theories at work, but I may
be lacking the skills with code.

Issue: The union queries I build are unrelated objects. When I use the code
from Allen Browne on my "AllFacts" search form, I am having trouble getting
the text box that would display search results have a Control Source as the
field from the union query. Am I being clear on this description? Whenever
I attempt to enter the field from the union query as the Control Source for
that text box, I get "#Name?"

The name of my union query is "UNION TEST."
The name of the field in the union query that I need is
"IncomeGet_CrisisAssistanceServices."

Hope I've described this correctly. Thanks for all of the help!

TJ
[quoted text clipped - 6 lines]

can also come from a very typical beginner's design mistake. Of course it's
okay if you know what you're doing!

 

[Gina Whipp]

TJ,

We are ALL happy you got it working!

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Got it!

I built a simple query based on UNION TEST, then appended this simple
query
into a table that is related to my search form. Keyword searching across
tables is working!

Huzzah!

TJ

Looks like I spoke too soon!

John, I have re-visited my design, and it seems that I really do need to
keep
these tables distinct and use the union query to conduct a search.

So far, I have things working out just fine. I have implemented and
tested
out the search interface at http://allenbrowne.com/ser-62.html (thanks
Gina!),
have built a union query across tables (thanks Lars!). The only kink now
is
incorporating the two. I think I understand the theories at work, but I
may
be lacking the skills with code.

Issue: The union queries I build are unrelated objects. When I use the
code
from Allen Browne on my "AllFacts" search form, I am having trouble
getting
the text box that would display search results have a Control Source as
the
field from the union query. Am I being clear on this description?
Whenever
I attempt to enter the field from the union query as the Control Source
for
that text box, I get "#Name?"

The name of my union query is "UNION TEST."
The name of the field in the union query that I need is
"IncomeGet_CrisisAssistanceServices."

Hope I've described this correctly. Thanks for all of the help!

TJ

The UNION query suggestion will work... but the need to do this
suggests
that

[quoted text clipped - 6 lines]

can also come from a very typical beginner's design mistake. Of course
it's
okay if you know what you're doing!

 

[Lars Brownies]

Glad it's working.

It sounds like you chose Allan's filter option. If so, you can probably base
your search form on UNION TEST. Then you don't need the extra query and
extra table.

Lars

Got it!

I built a simple query based on UNION TEST, then appended this simple
query
into a table that is related to my search form. Keyword searching across
tables is working!

Huzzah!

TJ

Looks like I spoke too soon!

John, I have re-visited my design, and it seems that I really do need to
keep
these tables distinct and use the union query to conduct a search.

So far, I have things working out just fine. I have implemented and
tested
out the search interface at http://allenbrowne.com/ser-62.html (thanks
Gina!),
have built a union query across tables (thanks Lars!). The only kink now
is
incorporating the two. I think I understand the theories at work, but I
may
be lacking the skills with code.

Issue: The union queries I build are unrelated objects. When I use the
code
from Allen Browne on my "AllFacts" search form, I am having trouble
getting
the text box that would display search results have a Control Source as
the
field from the union query. Am I being clear on this description?
Whenever
I attempt to enter the field from the union query as the Control Source
for
that text box, I get "#Name?"

The name of my union query is "UNION TEST."
The name of the field in the union query that I need is
"IncomeGet_CrisisAssistanceServices."

Hope I've described this correctly. Thanks for all of the help!

TJ

The UNION query suggestion will work... but the need to do this
suggests
that

[quoted text clipped - 6 lines]

can also come from a very typical beginner's design mistake. Of course
it's
okay if you know what you're doing!