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.

Improve this question
6
  • 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
    Commented Jul 3, 2015 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
    Commented Sep 24, 2015 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?
    – JustInTheWoods
    Commented Jan 8, 2016 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
    Commented Jan 20, 2016 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
    Commented Jan 7, 2017 at 16:34

2 Answers 2

Reset to default
5

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

Improve this answer
1
  • 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
    Commented Aug 31, 2016 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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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