Query question

W

wkomorow

This sounds like a very simple thing to do, but I can not make it work.
Basically, I would like the database to return entries regardless o
wich field the search term is in. Because many of my fields are memo
I can not use the find key. Fundamentally, I would like it to searc
as follows:


Field: CollectionRecord,*
Table: CollectionRecord
Criteria: Like "*" & [Enter a word] & "*"


Is there a simple way that I can do this.

Thanks so much
 
J

Jeff Boyce

That would be simple, were Access not designed to be a relational database
....

Having something of interest in any of a number of different fields presents
a strong warning flag -- there's a good chance your data structure is not
well-normalized. If you weren't using Access (or another relational
database), this would not necessarily be an issue.

Can you explain why you have more than one field that might contain what you
are looking for?

Good luck

Jeff Boyce
<Access MVP>
 
W

wkomorow

Sure, We have an archives database that includes a variety o
materials. There are for example letters - the letters have from an
to fields, they also have a subject fields, and they have a donated b
field. I want all cases in which Smith is in the from or the to or th
aubject or the donated by fields, or in some cases I may be looking fo
Smith to find nee Smith. The relationship is the collection to th
individual pieces - which folder belongs to which, what the materia
types are. However, these fields are and should be distinct, I jus
need to search among all fields to find Smith. In terms of using th
database to collect information about the collection and maintainin
the relationship among materials I am having no problem. I guess
don't understand why I can open a table and do a Find and get thi
information but I can not construct a very easy quer
 
P

Per Larsen

You could try to concatenate the columns (fields) in which the search term might be; however, as someone else said, you should probably reconsider your db design:

select ...
from ...
where col1 & col2 & col3 & colN... Like "*" & [Enter a word] & "*"

Hth
PerL
 
J

Jeff Boyce

One approach, if you only need to find (all) rows/records with (something
like) "Smith" in one of several fields would be to build a UNION query.

First, for each field that you would look in, create a query that returns
that field and the rowID. If you had, as in your example, four fields to
check, you'd have 4 queries, each with only two field in the query (rowID
and fieldX).

Now copy the SQL statement from each of these into a UNION query (SQL only),
separated by UNION (something like:)

SELECT a, b FROM YourTable
UNION
SELECT a, c FROM YourTable
UNION
....

Now create another query based on this UNION query, and add the
parameter/criterion --

Like * & [Enter your search string] & *

to the non-rowID field's criterion to allow searching for different strings.

Or, you could just use the Find function?!

Good luck

Jeff Boyce
<Access MVP>
 
J

John Vinson

This sounds like a very simple thing to do, but I can not make it work.
Basically, I would like the database to return entries regardless of
wich field the search term is in. Because many of my fields are memo,
I can not use the find key. Fundamentally, I would like it to search
as follows:


Field: CollectionRecord,*
Table: CollectionRecord
Criteria: Like "*" & [Enter a word] & "*"


Is there a simple way that I can do this.
Click to expand...

Only by putting the criterion under each and every field that you want
to search, on separate lines to use OR logic.

The need to do this makes me very uncomfortable about the logical
design of your tables, though. You wouldn't search a LastName field
for a street address, or vice versa! Normally each field in a table
refers to a specific, limited attribute of the Table's Entity; as
such, this kind of "global search" is quite uncommon.

Could you describe the nature of the data in the table and what you're
trying to accomplish? There may well be a better way.


John W. Vinson[MVP]
 
Top