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.
LVL 18
mgcITAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeCommented:
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.
0
mgcITAuthor Commented:
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
0
Dale FyeCommented:
There was a type in my original SQL, it should have read:
SELECT O.[OffName] AS Office
     , D.[DeptName] AS Department
     , [Cltnum] & "." & [CltEng] AS [Client Code]
     , CC.[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

Are there really two fields with names [CltCustCltID] and [CltCustID]?  If so, then I suspect the following might work.
SELECT O.[OffName] AS Office
     , D.[DeptName] AS Department
     , [Cltnum] & "." & [CltEng] AS [Client Code]
     , CC.[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 (SELECT CltCustCltID, CltCustID, CltCustValue 
           FROM dbo_CltCustom WHERE CltCustId = 108) 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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

mbizupCommented:
Try this:


SELECT dbo_Office.[OffName] AS Office, dbo_Department.[DeptName] AS Department, [Cltnum] & "." & [CltEng] AS [Client Code], q.[CltCustValue] AS [First Year Engagement]
FROM 
((dbo_Clients LEFT JOIN 
dbo_Office ON dbo_Clients.Cltoff = dbo_Office.OffID) 
LEFT JOIN dbo_Department ON dbo_Clients.Engdept = dbo_Department.DeptID) 
LEFT  JOIN (SELECT * FROM dbo_CltCustom WHERE dbo_CltCustom.CltCustId=108) q ON dbo_Clients.ID = q.CltCustCltId

GROUP BY dbo_Office.[OffName], dbo_Department.[DeptName], [Cltnum] & "." & [CltEng], q.[CltCustValue], q.CltCustId
ORDER BY dbo_Office.[OffName], dbo_Department.[DeptName], [Cltnum] & "." & [CltEng];

Open in new window

0
PortletPaulfreelancerCommented:
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
0
Dale FyeCommented:
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.
0
PortletPaulfreelancerCommented:
Thanks Fyed.
0
mgcITAuthor Commented:
Thank you!
0
mgcITAuthor Commented:
I have a follow-up Q if anyone is able to help... thanks.

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_28257562.html
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.