Like Query

[andream02]

Hello,

I'm trying to build a like query that will compare the Account Name in one table to another. Unfortunately, I can not change the data in the second table because the data in the second table are coming from registration forms which a filled out by the conference attendee.

Here is an example of the data. I want to take the Registration Form column and find LIKE values in the ACCOUNT Table. I tried to provide an example of how the Account Table could return more than one value, which is exactly what I want it to do.

Any thoughts on how to build the SQL for this? I'm having a hard time with this one and I'm sure it's something super easy. Appreciate any help.

<style type="text/css">
table.tableizer-table {
border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif;
font-size: 12px;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #ccc;
}
.tableizer-table th {
background-color: #104E8B;
color: #FFF;
font-weight: bold;
}
</style>

<table class="tableizer-table">
<tr class="tableizer-firstrow"><th>Registration Form</th><th>Account Table</th></tr>
<tr><td>Calcasieu Parish Public School System</td><td>Calcasieu Parish Public Schools</td></tr>
<tr><td>Putnam County School System</td><td>Putnam County School District</td></tr>
<tr><td> </td><td>Putnam County Schools</td></tr></table>

 

[SydneyGeek]

I'd build a table with just the main qualifying names (Calcasieu Parish, Putnam County, etc), add that to the query grid, and build the LIKE statements so that the other 2 tables both compare themselves to the short version.
To build that short descriptor (and assuming that School or Schools is always there) you could use something like
LEFT([Location],Instr(1,[Location],"School")-1)

Do this for both tables, pushing to the new table.
Append that to a new table with a single field called ShortLocation (Text, Primary Key).
If duplicates occur they won't be imported (and you will get an error message).

Denis