Selecting all records that match a regular expression (like GREP)

[richard.j.whitaker]

Hi All,

Forgive my ignorance -- I'm sure this is simple, but being new to
Access I don't know how to do it.... here goes.

I have a table with about 40 columns and a few thousand entries. It's
full of text, mostly addresses. I need to pull out every record in
which a regular expression exists, no matter in which column it is. EG
I want to pull out every record that has "road" in it no matter whether
the street is called "Worthy Road" or it's "Mr Road" who lives there.

The key thing is that I need to look over the whole table, not just in
one column.

For anyone who's familiar with Unix, I'm trying to do the equivalent of
a "grep" on the table.

Thanks in advance.

ps: the more elegant the answer the better!

 

[Rick B]

If you have several columns (should be called fields) that all might contain
addresses, then there is a pretty good chance your table design is wrong.
If each person can have one or several addresses, then you have a
one-to-many relationship and that is handled by two tables, not one.

Instead of having five or six columns to contain your addresses, you should
have a new table with fields for your address. Each "person" will have one
record in that table for every address they have. If they have six
addresses, then they would have six records.

TblCustomers
CustNumber
CustName
CustType
CustPhone
CustFax
etc.


TblAddresses
CustNumber
Address1
Address2
City
State
ZIP



Access does not have an easy way to do what you are asking using your
current structure, because that structure is not proper for a normalized
relational database. A piece of data such as "street address" should rarely
occur in more than one field.

In short, normalize your data, and then Access will work for you.

Hope that helps.

 

[astro_bod]

Thanks for the response Rick B.

If you have several columns (should be called fields) that all might contain
addresses, then there is a pretty good chance your table design is wrong.
If each person can have one or several addresses, then you have a
one-to-many relationship and that is handled by two tables, not one.

I understand the comment, however the structure of the table is not
wrong. I understand your idea of a separate address table -- and agree
with the case you cite --, however in my case every "person" can only
have one address, so there is no issue with regards this.

Let me expand: The structure of the table is not the issue, in fact it
is totally irrelevant and I shouldn't have mentioned it in the first
place. The question is how to filter *any* table by regular expressions
-- that's all I'm after. I would be very surprised if such a simple
task were not possible under Access.

My latest idea is to create a query that does something like:

*************************
if ([ field1 contains string] or [field2 contains string] ... [fieldN
contains string] ) then print whole record
*************************

The problem with this is there are 200+ different strings I need to
search for. I am looking for a way to automate this.

Thanks again for the comments, I hope i've expanded the explanation of
what I'm after now.

 

[John Spencer]

Do you need to search for the 200 plus strings simultaneously? Or are you
searching for them in individual instances?

You could concatenate all the fields together and do a wild card search
against that concatenated value.

SELECT ...
FROM YourTable
WHERE FieldA & "/" & FieldB & "/" & FieldC & "/" & FieldD Like "*" & [Enter
Your String] & "*"

I added the slash between fields so that if your search matches would not go
across fields unless your search string included a "/".

Thanks for the response Rick B.

If you have several columns (should be called fields) that all might
contain
addresses, then there is a pretty good chance your table design is wrong.
If each person can have one or several addresses, then you have a
one-to-many relationship and that is handled by two tables, not one.

I understand the comment, however the structure of the table is not
wrong. I understand your idea of a separate address table -- and agree
with the case you cite --, however in my case every "person" can only
have one address, so there is no issue with regards this.

Let me expand: The structure of the table is not the issue, in fact it
is totally irrelevant and I shouldn't have mentioned it in the first
place. The question is how to filter *any* table by regular expressions
-- that's all I'm after. I would be very surprised if such a simple
task were not possible under Access.

My latest idea is to create a query that does something like:

*************************
if ([ field1 contains string] or [field2 contains string] ... [fieldN
contains string] ) then print whole record
*************************

The problem with this is there are 200+ different strings I need to
search for. I am looking for a way to automate this.

Thanks again for the comments, I hope i've expanded the explanation of
what I'm after now.