Error in an INNER JOIN

[Flo100]

My Query is as follows:

Select [Q] .[QUE$TOR version name],[Q].[Cost Tab], sum([Q].[Cost]) as Cost FROM [QUESTOR Run tbl] as Q INNER JOIN [Project info tbl] as [P] ON [P].[Project ID]=[Q].[Project ID] INNER JOIN (Select [P].[Project ID],[P].[Onshore only] FROM [Project info Tbl] as P INNER JOIN [QUESTOR Run Tbl] as Q ON [P].[Project ID]=[Q].[Project ID] GROUP BY [P].[Project ID],[P].[Onshore only] HAVING [P].[Onshore only]=yes) as Q1 ON [P].[Project ID]=[Q1].[Project ID] GROUP BY [Q].[QUE$TOR Version name],[Q].[Cost Tab] HAVING ([Q].[QUE$TOR Version name])=”7.3” AND ([Q].[Cost Tab] NOT LIKE “%drilling%”)


Can somebody tell me what is wrong in this join. Thank you.

[JKing]

What error message are you receiving? It will be easier to find the error if we know what it is.

[Flo100]

What error message are you receiving? It will be easier to find the error if we know what it is.

Syntax error(missing operator) in query expression [P].[Project ID]=[Q].[Project ID] (Select [P].[Project ID],[P].[Onshore only] FROM [Project info Tbl] as P INNER JOIN [QUESTOR Run Tbl] as Q ON [P].[Project ID]=[Q].[Project ID] GROUP BY [P].[Project ID],[P].[Onshore only] HAVING [P].[Onshore only]=yes)

This is the error.

[Flo100]

Syntax error(missing operator) in query expression [P].[Project ID]=[Q].[Project ID] (Select [P].[Project ID],[P].[Onshore only] FROM [Project info Tbl] as P INNER JOIN [QUESTOR Run Tbl] as Q ON [P].[Project ID]=[Q].[Project ID] GROUP BY [P].[Project ID],[P].[Onshore only] HAVING [P].[Onshore only]=yes)

This is the error.

My actual query should look something like this:

Select Q2.[QUE$TOR version name], sum(Q2.[Cost]) from (Select [Q] .[QUE$TOR version name],[Q].[Cost Tab], sum([Q].[Cost]) as Cost FROM [Project Run tbl] as [P] INNER JOIN (Select [P].[Project ID],[P].[Onshore only] FROM [Project Run tbl] as P INNER JOIN [QUESTOR Run Tbll] as Q ON [P].[Project ID]=[Q].[Project ID] GROUP BY [P].[Project ID],[P].[Onshore only] HAVING [P].[Onshore only]=yes) as Q1 ON [P].[Project ID]=[Q1].[Project ID] INNER JOIN [QUESTOR Run tbl] as Q ON [P].[Project ID]=[Q].[Project ID] GROUP BY [Q].[QUE$TOR Version name],[Q].[Cost Tab] HAVING ([Q].[QUE$TOR Version name])=”7.3” AND ([Q].[Cost Tab] NOT LIKE “%drilling%”)) as Q2 GROUP BY [Q2.[QUE$TOR Version name]



I am trying to get the projects that are onshore only from project info tbl and sum the costs for version 7.3 whose cost tab value does not say drilling. now because this is a multivalued column i am not able to sum in one query so i am querying the result third time again on Q2 for sum...............i donno if this gave you even a pinch of idea about what i want to do. But i hope so, please let me know and i can write a complete explanation.

[Flo100]

My actual query should look something like this:

Select Q2.[QUE$TOR version name], sum(Q2.[Cost]) from (Select [Q] .[QUE$TOR version name],[Q].[Cost Tab], sum([Q].[Cost]) as Cost FROM [Project Run tbl] as [P] INNER JOIN (Select [P].[Project ID],[P].[Onshore only] FROM [Project Run tbl] as P INNER JOIN [QUESTOR Run Tbll] as Q ON [P].[Project ID]=[Q].[Project ID] GROUP BY [P].[Project ID],[P].[Onshore only] HAVING [P].[Onshore only]=yes) as Q1 ON [P].[Project ID]=[Q1].[Project ID] INNER JOIN [QUESTOR Run tbl] as Q ON [P].[Project ID]=[Q].[Project ID] GROUP BY [Q].[QUE$TOR Version name],[Q].[Cost Tab] HAVING ([Q].[QUE$TOR Version name])=”7.3” AND ([Q].[Cost Tab] NOT LIKE “%drilling%”)) as Q2 GROUP BY [Q2.[QUE$TOR Version name]



I am trying to get the projects that are onshore only from project info tbl and sum the costs for version 7.3 whose cost tab value does not say drilling. now because this is a multivalued column i am not able to sum in one query so i am querying the result third time again on Q2 for sum...............i donno if this gave you even a pinch of idea about what i want to do. But i hope so, please let me know and i can write a complete explanation.

Is my intention in joining three queries into one not right?

[MMcCarthy]

OK your subselect on a subselect is very messy. What are you trying to do and what is the structure of your tables.

[Flo100]

OK your subselect on a subselect is very messy. What are you trying to do and what is the structure of your tables.

My main problem is that the table structure is not proper and I need to work on it as it is given. But I figured out how do it. I simply created each subquery as another query. Thank you very much for your help and concern.