CODEWITHSUNDEEP
sqlms-accessleft-join
fidjeland
fidjeland

Reputation: 35

Access SQL left join - access removes parenthesis

I have written a SQL query that do what I want:

SELECT 
    Tag.ID, Tag.Type, COUNT(Cable.Type) 
FROM 
    Tag 
LEFT JOIN 
    Cable ON (Cable.Type = Tag.Type AND Cable.Use = "KW")
GROUP BY 
    TagID, Tag.Type

Problem is that upon saving, MS Access removes the parenthesis around the ON clause. When I try to reopen the query, I cannot get the table as the query is not working without parenthesis. Also, I cannot edit the SQL as the query isn't working.

Q1: Are there any ways I can prevent Access from removing the parenthesis?

Q2: Any suggestions on how to reformulate the SQL to avoid the problem? I have tried the following:

SELECT 
    Tag.ID, Tag.Type, COUNT(Cable.Type) 
FROM 
    Tag 
LEFT JOIN 
    Cable ON Cable.Type = Tag.Type
WHERE 
    Cable.Use = "KW" OR Cable.Use Is Null
GROUP BY 
    TagID, Tag.Type

But, this takes away the entries of table "Tag" without matching entries in table "Cable", and I don't wont that; I want the counting to show "0" in these cases.

I know it is possible to force the sql query with the parenthesis into Access using VBA, but it is cumbersome to do often, and I have many of the similar cases.

Upvotes: 3

Views: 167

Answers (2)

moni_dragu
moni_dragu

Reputation: 1163

Use Nz function to resolve null entries 

SELECT Tag.ID, Tag.Type, nz(COUNT(Cable.Type),0)
FROM Tag LEFT JOIN Cable ON Cable.Type = Tag.Type
WHERE nz(Cable.Use,"KW") = "KW"
GROUP BY TagID, Tag.Type

Upvotes: 0

Joe Taras
Joe Taras

Reputation: 15389

Try this:

SELECT 
    Tag.ID, Tag.Type,
    NZ((SELECT COUNT(Cable.Type) 
    FROM Cable
    WHERE Cable.Type = Tag.Type
    AND Cable.Use = "KW"),0)
FROM Tag

Upvotes: 1

Related Questions