Filtering query based on *string* in another table

[Nesli]

I believe this should be fairly easy, but I just couldn't figure it out.
I have a main data table (table 1) with client account names and related data. Every month, I have to check for new accounts that belongs to our top clients. In another table (table 2) , I have a list of the account names of our top clients. I want to create a query that will filter the main data table (table 1) and give me all records where the account name matches the strings in the table 2.
For example, I have "Geico" as a record in table 2, and I want to be able to get all the records that has "*Geico*" string in the account name field.

Table 1:
Account Name:
Geico/Baltimore
Washington Geico
Geico Hartford

Table 2:
Filter:
Geico

Thanks for all your suggestions.

 

[mdmilner]

You do this by joining the fields.
Open up a new query in Design Mode.
Add the two tables that you want to use.

For the primary data table, just grab the asterisk (all fields) for right now.
For the other table, click on the company name field and drag it over to the same field in the primary table - this opens up the relationship dialog window.
Now, right click the line that forms and select the option that says "Include all fields from the (table with the top company names only) and only those fields that match in the (Primary table)

Run it.

This will show you just the entries that have a company name match regardless of how many it ends up being. If you have reports to build, you'll probably use that query as a basis (Recordsource) for those reports.

As information, after you've done this, right click the blue bar at the top of the query while you have it open in Design mode. Select SQL View. You'll see something like:

SELECT tblname.* FROM tblname2 LEFT JOIN tblname ON tblname2.fieldname1 = tblname.fieldname1;

That's what it looks like in SQL. It's called a Join (LEFT JOIN for my example). Most "Filtering" options filter out all but a single field string.

Oops, just occurred to me that this won't work as-is. You're wanting to use partial strings to match it as this is looking for exact matches.

The SQL syntax to match partial strings is LIKE.
Works like:

SELECT * FROM tblName WHERE fieldname1 LIKE '*string_match*'

My first choice to do something like this would be to use a VBA procedure that opens a recordset containing the 2nd table. It'd then iterate thru the table and execute SQL.

If I needed to save the data, I'd set it up to dynamically create an APPEND query and send the values over to a temporary table which I'd later purge when I'm done.

I don't think you can use the LIKE parameter in a JOIN. I also don't think that Access allows you to use a subquery to send multiple values into a query thus working as a join.

Anybody else have an easy way?

Mike