Duplicate Values Between Two Tables

Is your DBID Alias correct?  Go into the Settings of the table; scroll to the bottom to Advanced and then at the very bottom of that screen is your Table's "Real" name (not the name you have on the menu bar).  That could be why you're getting an issue with your table name not being recognized.

Furthermore, while formula queries are amazing; and can solve for a large number of use cases; they are prone to size-calculation restrictions; meaning that if either of your two tables has enough data in them the formula will simply be unable to calculate (without warning). 

An alternative strategy is to use Pipelines to create "join" table records which can create a record for each associate member if the Provider ID exists in the providers table, so there is both a [related provider] and a [related associate provider] in the join-table.  Then you can use the standard summary/lookup fields within the relationship to determine IF (has summary function) there are Preferred Providers for that Associate Member or how many (count function) and even which ones they are (summary combined text). 

If your table of Preferred Providers or Associate Members is going to grow very large, you might want to consider this strategy now; rather than waiting for the Formula Query to eventually fail to calculate at some random date in the future.