Help with Access query

Referencing this previous Q:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_28255228.html

The CltCustom (Client Custom) table is giving me trouble.  It's intent was to allow infinite number of custom fields for the Client table. Structure is simple:

CltCustId = ID of the custom field
CltCustCltId = ID of the Client from Client table
CltCustValue = Value of custom field

I am building a query that returns basic Client info, including some info from custom fields and am having trouble with the joins.  I need all Client records, plus some custom data that may only exist for some clients

I have this from my previous Q:
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];

Now if I also wanted to add another returned value WHERE CltCustId= 109 how would I do that?.... there may be 3 or 4 more also... 110, 111, 112

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.

PortletPaulfreelancerCommented:
I truly believe you will regret this design. Whilst the concept of an infinite number of "fields" sounds attractive - the underlying complexity it produces will eventually drive you insane (and the performance of your queries towards infinite response times).

Here's what I think a few more "infinite custom fields" will look like in that query
SELECT
        dbo_Office.[OffName]      AS Office
      , dbo_Department.[DeptName] AS Department
      , [Cltnum] & "." & [CltEng] AS [Client Code]
      , q108.[CltCustValue]       AS [First Year Engagement]
      , q109.[CltCustValue]       AS [q109]
      , q110.[CltCustValue]       AS [q110]
      , q111.[CltCustValue]       AS [q111]
      , q112.[CltCustValue]       AS [q112]
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 ) q108  ON dbo_Clients.ID = q108.CltCustCltId
LEFT JOIN ( SELECT *  FROM dbo_CltCustom  WHERE dbo_CltCustom.CltCustId = 109 ) q109  ON dbo_Clients.ID = q109.CltCustCltId
LEFT JOIN ( SELECT *  FROM dbo_CltCustom  WHERE dbo_CltCustom.CltCustId = 110 ) q110  ON dbo_Clients.ID = q110.CltCustCltId
LEFT JOIN ( SELECT *  FROM dbo_CltCustom  WHERE dbo_CltCustom.CltCustId = 111 ) q111  ON dbo_Clients.ID = q111.CltCustCltId
LEFT JOIN ( SELECT *  FROM dbo_CltCustom  WHERE dbo_CltCustom.CltCustId = 112 ) q112  ON dbo_Clients.ID = q112.CltCustCltId

GROUP BY
         dbo_Office.[OffName]
       , dbo_Department.[DeptName]
       , [Cltnum] & "." & [CltEng]
       , q108.[CltCustValue]
       , q109.[CltCustValue]
       , q110.[CltCustValue]
       , q111.[CltCustValue]
       , q112.[CltCustValue]
ORDER BY
        dbo_Office.[OffName]
      , dbo_Department.[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
hnasrCommented:
Start with current status.
Upload a simple database and explain the requirement, showing example with few records.
0
mgcITAuthor Commented:
PortletPaul: I get a syntax error with your query

"Syntax error (missing operator) in query expression 'dbo.Clients.ID = q108.CltCustCltID...."

Btw, it's not my database design.  This is an accounting software application that I've been tasked with writing some custom reports for.  Thanks.
0
PortletPaulfreelancerCommented:
This was in your question, and I assume it works

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

does this work? all it does is change q to q108
LEFT  JOIN (SELECT * FROM dbo_CltCustom WHERE dbo_CltCustom.CltCustId=108) q108 ON dbo_Clients.ID = q108.CltCustCltId

from that point on the other left joins also just use a different alias
q108
q109
q110
q111

try it one join at a time perhaps.
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.