Query Match on any part of a field

[Guest]

How can I query to find records that match on any part of a field?

Ex:
Table 1 Contract# Contract Date
1000-abc 1/1/06

Table 2 Contract# ContractName
General-1000-abc-1 Department Supplies

I would want the contract # from table 1 to return as a match and see both
contract # fields from table 1 and table 2.

Thank you

 

[Allen Browne]

One of the basic rules of data normalization is that your fields must be
atomic. That means, store only ONE thing in any field. Instead of putting
City and State in one field, use two.

Your question suggests that you have multiple pieces of information in one
of the fields, and as a result you need to make partial matches. The
solution will be to break it down into multiple fields, so you can create
appropriate relationships between the tables and query the data efficiently.

It is actually possible to put both tables into the query, and use Like with
* as the wildcard character before and after the values to get matches. For
example, in the criteria under

.[Contract#] you could put:
Like "*" & [Table1].[Contract#] & "*"
This is not only inefficient, but it also won't work for the example you
give, since the 1000 is inserted between the different values.

(And that's before you try to cope with the fact that Microsoft broke Access
2000 and later so that it fails to match correctly on fields that contain
the hyphen character.)

 

[Guest]

Thank you - I was afraid I was stuck on this one. The problem is I am
repairing a database that was improperly constructed so that the contract #
was manually typed into table1 - and of course human error decided to not
bother to type the first or last (or maybe both) parts of the contract #.
That problem has now been corrected so that the contract number pulls
directly from Table 2, but my task is to go back and identify all of the
contract numbers that were typed with the middle section only and get the
full contract number into place.

So all I can se that I can do is use EDIT>FIND and search on part of a
field to locate my problems. Very manual, very time consuming.

Any other ideas are welcome! thanks

One of the basic rules of data normalization is that your fields must be
atomic. That means, store only ONE thing in any field. Instead of putting
City and State in one field, use two.

Your question suggests that you have multiple pieces of information in one
of the fields, and as a result you need to make partial matches. The
solution will be to break it down into multiple fields, so you can create
appropriate relationships between the tables and query the data efficiently.

It is actually possible to put both tables into the query, and use Like with
* as the wildcard character before and after the values to get matches. For
example, in the criteria under

.[Contract#] you could put:
Like "*" & [Table1].[Contract#] & "*"
This is not only inefficient, but it also won't work for the example you
give, since the 1000 is inserted between the different values.

(And that's before you try to cope with the fact that Microsoft broke Access
2000 and later so that it fails to match correctly on fields that contain
the hyphen character.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

How can I query to find records that match on any part of a field?

Ex:
Table 1 Contract# Contract Date
1000-abc 1/1/06

Table 2 Contract# ContractName
General-1000-abc-1 Department
Supplies

I would want the contract # from table 1 to return as a match and see both
contract # fields from table 1 and table 2.

Thank you

 

[Allen Browne]

You could use Instr() to locate the hypen, Left(), Right(), and Mid() to
parse the sections, and construct a string that concatenates these values
with the "*" wildcard and the Like operator in the Criteria.

It could take several passes to identify the various combinations.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thank you - I was afraid I was stuck on this one. The problem is I am
repairing a database that was improperly constructed so that the contract
#
was manually typed into table1 - and of course human error decided to not
bother to type the first or last (or maybe both) parts of the contract #.
That problem has now been corrected so that the contract number pulls
directly from Table 2, but my task is to go back and identify all of the
contract numbers that were typed with the middle section only and get the
full contract number into place.

So all I can se that I can do is use EDIT>FIND and search on part of a
field to locate my problems. Very manual, very time consuming.

Any other ideas are welcome! thanks

One of the basic rules of data normalization is that your fields must be
atomic. That means, store only ONE thing in any field. Instead of putting
City and State in one field, use two.

Your question suggests that you have multiple pieces of information in
one
of the fields, and as a result you need to make partial matches. The
solution will be to break it down into multiple fields, so you can create
appropriate relationships between the tables and query the data
efficiently.

It is actually possible to put both tables into the query, and use Like
with
* as the wildcard character before and after the values to get matches.
For
example, in the criteria under

.[Contract#] you could put:
Like "*" & [Table1].[Contract#] & "*"
This is not only inefficient, but it also won't work for the example you
give, since the 1000 is inserted between the different values.

(And that's before you try to cope with the fact that Microsoft broke
Access
2000 and later so that it fails to match correctly on fields that contain
the hyphen character.)

How can I query to find records that match on any part of a field?

Ex:
Table 1 Contract# Contract Date
1000-abc 1/1/06

Table 2 Contract# ContractName
General-1000-abc-1 Department
Supplies

I would want the contract # from table 1 to return as a match and see
both
contract # fields from table 1 and table 2.

 

[Guest]

Thank you, yes, that will work for the left side (and will be a big help),
which has the 5 characters up to the hyphen most frequently missing. The
right side truncation has no rhyme or reason, sometimes missing 1 character,
sometimes more, and there isnt any associated hyphen.

You could use Instr() to locate the hypen, Left(), Right(), and Mid() to
parse the sections, and construct a string that concatenates these values
with the "*" wildcard and the Like operator in the Criteria.

It could take several passes to identify the various combinations.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thank you - I was afraid I was stuck on this one. The problem is I am
repairing a database that was improperly constructed so that the contract
#
was manually typed into table1 - and of course human error decided to not
bother to type the first or last (or maybe both) parts of the contract #.
That problem has now been corrected so that the contract number pulls
directly from Table 2, but my task is to go back and identify all of the
contract numbers that were typed with the middle section only and get the
full contract number into place.

So all I can se that I can do is use EDIT>FIND and search on part of a
field to locate my problems. Very manual, very time consuming.

Any other ideas are welcome! thanks

One of the basic rules of data normalization is that your fields must be
atomic. That means, store only ONE thing in any field. Instead of putting
City and State in one field, use two.

Your question suggests that you have multiple pieces of information in
one
of the fields, and as a result you need to make partial matches. The
solution will be to break it down into multiple fields, so you can create
appropriate relationships between the tables and query the data
efficiently.

It is actually possible to put both tables into the query, and use Like
with
* as the wildcard character before and after the values to get matches.
For
example, in the criteria under

.[Contract#] you could put:
Like "*" & [Table1].[Contract#] & "*"
This is not only inefficient, but it also won't work for the example you
give, since the 1000 is inserted between the different values.

(And that's before you try to cope with the fact that Microsoft broke
Access
2000 and later so that it fails to match correctly on fields that contain
the hyphen character.)

How can I query to find records that match on any part of a field?

Ex:
Table 1 Contract# Contract Date
1000-abc 1/1/06

Table 2 Contract# ContractName
General-1000-abc-1 Department
Supplies

I would want the contract # from table 1 to return as a match and see
both
contract # fields from table 1 and table 2.