[RESOLVED] [2005] SQL Syntax

I'm trying to select info from multiple tables and this isn't working:

Code:

---

"SELECT distinct Production.API " _
                & "FROM (well_header JOIN production)" _
                & "WHERE Not production.api = well_header.api" _
                & "ORDER BY Production.API;"

---

I originally had this because I know that it does work for sure but when I put it in VB, I get a syntax error stating the join isn't correct but it works when I execute it from Access

Code:

---

"SELECT distinct Production.API " _
                & "FROM (well_header, production)" _
                & "WHERE Not production.api = well_header.api" _
                & "ORDER BY Production.API;"

---

Any ideas anybody??

Moved to Database Development

Are you trying to select something from one table that IS NOT in another table?

Is this what you are tring?

Select Production.API From Production Where Production.API Not In (Select well_header.api From Well_Header) Order By Production.API

Yes, I'm trying to select data that is in the Production table but not in the wellHeader table

Gary went in the direction I was intending to go. Have you tried his suggestion?

Thanks Gary! That worked