5

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.

  • It is SQL, not VBA. Did you build the expression with the query builder or type it? What is the ObjectID field setting option for the tool? In a Many to Many relationship it has to create new ObjectIDs in memory and the correct options is critical. How large are the tables? Are all the field types the same for each pair of matching fields? – Richard Fairhurst Jul 3 '15 at 0:40
  • I have the same issue even without using multiple query criteria connected by AND, both with ArcGIS 10.2.2 and 10.3. Is this a ArcGIS 10 issue? – tinlyx Sep 24 '15 at 22:20
  • I am getting the same error message about the field while trying to do a 1:M query. Did you ever figure it out? – jmpreiks Jan 8 '16 at 21:01
  • I' m having the same problem while trying to do a one to mane query. Did anyone find a solution? – Dimitris Kasabalis Jan 20 '16 at 11:49
  • 2
    I am using Arcmap 10.4 and i am facing the same issue. I used the same tool in ArcGIS Pro and it worked fine. It seems there is a bug in Arcmap. – Firas Ghandour Jan 7 '17 at 16:34
5

Have you tried changing the double quotes ("") to single quotes (' ')? That helped my SQL code run smoothly.

  • Thanks! Single quotes ( 'table.field' ) worked for me when the inputs were from a File Geodatabase despite the documentation saying to use no delimiter or double quotes. – Adam Aug 31 '16 at 15:47
2

"Rivers_Streams_2014.ComID" is being read as the field name, when it is not - ComID is the field name.

Try re-writing as "Rivers_Streams".'ComID' or as "Rivers_Streams"."ComID"

Do this for all the field names you have referenced and you should be in business

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.