asked on

Help with Access query

I"m having an issue with this Query:

SELECT dbo_Office.[OffName] AS Office, dbo_Department.[DeptName] AS Department, [Cltnum] & "." & [CltEng] AS [Client Code], dbo_CltCustom.[CltCustValue] AS [First Year Engagement]
FROM ((dbo_Clients INNER JOIN dbo_Office ON dbo_Clients.Cltoff = dbo_Office.OffID) INNER JOIN dbo_Department ON dbo_Clients.Engdept = dbo_Department.DeptID) INNER JOIN dbo_CltCustom ON dbo_Clients.ID = dbo_CltCustom.CltCustCltId
GROUP BY dbo_Office.[OffName], dbo_Department.[DeptName], [Cltnum] & "." & [CltEng], dbo_CltCustom.[CltCustValue], dbo_CltCustom.CltCustId
HAVING (((dbo_CltCustom.CltCustId)=108))
ORDER BY dbo_Office.[OffName], dbo_Department.[DeptName], [Cltnum] & "." & [CltEng];

Specifically this line: HAVING (((dbo_CltCustom.CltCustId)=108))

I want all client records to be returned even if there is no matching value in the CltCustom table. Very few client records have a corresponding value in the CltCustom table. For the ones that do, I want the value returned, otherwise the field would just be blank. How would I do that?

Thanks.

Dale Fye

In order to get all of the clients from dbo_Clients and NULLS from the other tables where the dbo_Clients.ID does not have a match in those tables, you will have to use an outer join between the two tables, and removing the Having clause. Try this:

SELECT O.[OffName] AS Office
     , D.[DeptName] AS Department
     , [Cltnum] & "." & [CltEng] AS [Client Code]
     , C.[CltCustValue] AS [First Year Engagement]
FROM ((dbo_Clients as C 
LEFT JOIN dbo_Office as O ON C.Cltoff = O.OffID) 
LEFT JOIN dbo_Department as D ON C.Engdept = D.DeptID) 
LEFT JOIN dbo_CltCustom as CC ON C.ID = CC.CltCustCltId
GROUP BY O.[OffName], D.[DeptName], [Cltnum] & "." & [CltEng], CC.[CltCustValue], CC.CltCustId
ORDER BY O.[OffName], D.[DeptName], [Cltnum] & "." & [CltEng];

Open in new window

I would recommend that you explicitly state which table the [cltnum] and [CltEng] fields belong to as well.

mgcIT

ASKER

Thanks for the response. If I remove the HAVING clause, how do I filter results?

HAVING (((dbo_CltCustom.CltCustId)=108))

I only want the value of CltCustValue if CltCustId = 108

ASKER CERTIFIED SOLUTION

Dale Fye

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

mbizup

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PortletPaul

no points pl. just some points (and a question also)

Use a HAVING clause on aggregated values, use a WHERE clause on un-aggregated info.
>> your original could have used WHERE (((dbo_CltCustom.CltCustId)=108))
>> 'having' is done after the aggregations, 'where' is conducted before them, so it reduces the effort needed too :)
>> a single group by query may use both 'where' and 'having', by the way

As noted already you need an "outer join" for your requirements
and most of us will recommend a LEFT OUTER JOIN (or simply LEFT JOIN as seen above)
once you introduce an "outer join" there is the possibility that some values can be be NULL
and due to this, take care with any WHERE conditions to allow the nulls to 'survive' into the final results.
i.e.: as shown in slightly different ways above, you move that where filter from its typical position at the end, to a "join condition" - or as in the following example to a subquery:

LEFT JOIN (SELECT * FROM dbo_CltCustom WHERE dbo_CltCustom.CltCustId=108) q
ON dbo_Clients.ID = q.CltCustCltId

------- Q -------
My question (to others here) is:
Q: Doesn't Access allow direct join conditions like this?

LEFT JOIN dbo_CltCustom q ON dbo_Clients.ID = q.CltCustCltId AND q.CltCustId=108

Dale Fye

Paul,

Don't think so, but have never tried. I had never seen that syntax until I looked at one of your recent solutions to a SQL Server query question.

PortletPaul

Thanks Fyed.

mgcIT

ASKER

Thank you!

mgcIT

ASKER

I have a follow-up Q if anyone is able to help... thanks.

https://www.experts-exchange.com/questions/28257562/Help-with-Access-query.html