Query All Fields

[snax500]

In Access20007, how can I create a query that searches all fields
without using the criteria rows. For example, I want to find the word
"apple" in any instance in my table in any field. My table has 20
fields. Is there any easier way than keep inserting Like "*apple*" on
20 rows in the criteria section of the query.

Thanks

 

[boblarson]

You're not going to like this response, but the answer is "normalize" since
the sound of what you're asking for strikes as not normalized data. Of
course I could be wrong, but some more info about the table structure would
be required to definitively answer that. But typically when someone is
looking for the same word in more than one field it means that they don't
have their tables properly designed.


--
Bob Larson
Access World Forums Administrator

Tutorials at http://www.btabdevelopment.com

__________________________________

 

[Jerry Whittle]

The 'easy" way is to have all the 'fruit' in one column. Then you would only
need to search one field.

Since you have apples in more than one column, you will need to search all
fields.

However you could use the standard Find function if you only need to search
at table level. This won't help for forms and reports. Plus it can be VERY
slow if there are more than just a few records.

 

[BruceM]

Maybe you could concatenate the necessary fields, then search for "apple" in
that field:

MishMash: [Field1] & [Field2] & [Field3]

The Criteria for MishMash would be:
Like "*" & "apple" & "*"

However, it could well be that, as other have suggested, you have some
normalization issues in the database.

 

[snax500]

I am actually looking for vendor names that may be in all fields. I
get the data from the accounting general ledger and it does not need
to be normailzed. The data is the same every month. The vendor name
may show up many times and I need to export to Excel so I need to do a
query instead of a find. Any other ideas.

Thanks

 

[John W. Vinson]

I am actually looking for vendor names that may be in all fields. I
get the data from the accounting general ledger and it does not need
to be normailzed. The data is the same every month. The vendor name
may show up many times and I need to export to Excel so I need to do a
query instead of a find. Any other ideas.

You can create a permanent stored query with the criterion

[Enter vendor name:]

on each of the twenty or whatever fields (on separate lines to use OR logic),
and use the stored query. You don't need to rebuild the query every time!