SQL statment

[Bee]

Hi,

I have typed this SQL in a query; however, access doesn't seem to like it. Does anyone have any idea why please?

SELECT s.Name, p.Phase_no, h.House_ID
FROM Site AS s INNER JOIN (House AS h LEFT JOIN Phase AS  p ON h.Phase_No = p.Phase_no) ON s.Site_No = p.Site_No;

Thanks,
B

 

[FoFa]

Try this:

SELECT s.[Name], p.Phase_no, h.House_ID
FROM Site AS s 
INNER JOIN House AS h  ON s.Site_No = p.Site_No
LEFT JOIN Phase AS p ON h.Phase_No = p.Phase_no;

 

[Bee]

Try this:

SELECT s.[Name], p.Phase_no, h.House_ID
FROM Site AS s 
INNER JOIN House AS h  ON s.Site_No = p.Site_No
LEFT JOIN Phase AS p ON h.Phase_No = p.Phase_no;

I wonder why you enclosed Name in brackets?

 

[Banana]

Probably because it's a reserved word by Access. If you use a reserved word, it could confuse Access into thinking you wanted to do something else.

I'd encourage you to rename your field to some word that Access doesn't use.

Here's a Google Search for lists of reserved words

 

[Bee]

Try this:

SELECT s.[Name], p.Phase_no, h.House_ID
FROM Site AS s 
INNER JOIN House AS h  ON s.Site_No = p.Site_No
LEFT JOIN Phase AS p ON h.Phase_No = p.Phase_no;

Access keeps returning this error:
Syntax error (missing operator) in query expression 's.Site_No = p.Site_No....etc to the rest of the query'

I tried adding brackets to the joins; however, it's still returning the same error.

 

[reginab]

Why don't you just use the query grid and design it there? Once you create the query if you need the SQL text, you can copy it and use it elsewhere.

 

[Bee]

Why don't you just use the query grid and design it there? Once you create the query if you need the SQL text, you can copy it and use it elsewhere.

I tried that. Actually, my query is using a left join. i want to include all records in House table and only the matching records in the phase table.

When I use the grid and try to save it, it says: Join expression not supported

 

[FoFa]

Join expression is supported. Just double clik on the join arrow and it allows to you select the type of join you want.
You problem (I didn't notice before) is you are inner joining a table to a left joined table.
Plus your join of House does not include a column from House
SELECT s.[Name], p.Phase_no, h.House_ID
FROM Site AS s
INNER JOIN House AS h ON s.Site_No = p.Site_No <---- needs a column from h for the join
LEFT JOIN Phase AS p ON h.Phase_No = p.Phase_no;

 

[Bee]

Join expression is supported. Just double clik on the join arrow and it allows to you select the type of join you want.
You problem (I didn't notice before) is you are inner joining a table to a left joined table.
Plus your join of House does not include a column from House
SELECT s.[Name], p.Phase_no, h.House_ID
FROM Site AS s
INNER JOIN House AS h ON s.Site_No = p.Site_No <---- needs a column from h for the join
LEFT JOIN Phase AS p ON h.Phase_No = p.Phase_no;

I double clicked on the join arrow and selected the right type of join, but for some reason it still return the same error.

 

[FoFa]

Did you fix the join on house? maybe it is a typo, but I don't se how the designer would even you let you save that with the error. Go into SQL, and copy/paste it again.

 

[Bee]

Did you fix the join on house? maybe it is a typo, but I don't se how the designer would even you let you save that with the error. Go into SQL, and copy/paste it again.

The designer did not let me save it with the error. What's a "typo"?

 

[raskew]

Bee,

...What's a "typo"?

. It's a typographical error, e.g., you hit 'r' when you meant to hit '5'.

Bob

 

[Bee]

Bee,

. It's a typographical error, e.g., you hit 'r' when you meant to hit '5'.

Bob

Bob,

alright, I see.

Thank you,
B

 

[GORE]

Name is a reserved word in SQL statements