I have two tables that I have connected via an attributed relationship class of the cardinality Many-to-Many. One is a table of water body (rivers) features and the other is a series of pollutants the water bodies have been assessed for. All of these tables (river table, attributed relationship class, pollutants table) are located in the same file geodatabase. The reason for the many-to-many relationship is that one water body can impaired by many pollutants and one pollutant can impair many water bodies. In ArcGIS 9.3, I was able to use the "Make Query Table" tool to link these tables up and then query based on a single pollutant. Here is an example of the VBA or SQL code used to do this:
"Rivers_Streams_2014.ComID" = "rc_RiversStreams_2014.ComID_FK" AND "IR2014_RiversStreams.ID" = "rc_RiversStreams_2014.ID_FK" AND "IR2014_RiversStreams.Cause_Grouping" = 'Bacteria'
Now in ArcGIS 10.2.1, it keeps giving me an error message stating that
"An expected field was not found or could not be retrieved properly."
I'm not sure what I'm doing wrong as I've tried tweaking what options I select within the Make Query Table tool about 20 different ways. My guess is that there is a problem with my expression because the "verify" option always tells that it won't work. (Interestingly enough, it used to do the same thing when I had ArcGIS 9.3 and yet it still worked.)
Basically, what I'm trying to do is have this tool query out only those water bodies that have an assessment for a specified pollutant and be able to display them as a map layer.