[RESOLVED] [2005] SQL Syntax

[onlyGirl]

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

"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

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

Any ideas anybody??

[Hack]

Moved to Database Development

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

[GaryMazzone]

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

[onlyGirl]

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

[Hack]

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

[onlyGirl]

Thanks Gary! That worked