DB query where some values not found in joined table 1

[guitarzan]

I'm having troubles with an access db query, not sure how to get out what I want. Here's a stripped down description of the tables:

FirmCustDef Table Sample (id is the primary key):
[tt]id FCustDesc
111 "Extended"
112 "DateRecveived"
113 "DateCompleted"[/tt]

CltDUE Table sample (CDID is the primary key):
[tt]CDID CDDescription
1000 "Project A"
1001 "Project B"[/tt]

CltDueCustom Table sample:
CDCustCDID (relates to CltDUE.CDID, defining which Project this value is for)
CDCustID (relates to FirmCustDef.ID, defining which custom field this value is for)
CDCustValue (value of this custom field)

[tt]CDCustCDID CDCustID CDCustValue
1000 111 "Yes"
1000 112 "01/01/2012"
1000 113 "03/01/2012"
1001 112 "01/01/2012"[/tt]

I have only a read-only link to the database, and I want a query that shows one row per project, showing the Project ID, Project Description, And "Extended" value (CDCustID = 111), or blank in "Extended" if it was not found.

if CltDueCustom contained rows for all custom fields (as it does when CDID = 1000), I could use something like this to pull a single project:

SELECT dbo_CltDue.CDID AS ProjectID, dbo_CltDue.CDDescription AS ProjectDescription, dbo_CltDueCustom.CDCustValue AS Extended
FROM dbo_CltDue LEFT JOIN dbo_CltDueCustom ON dbo_CltDue.CDID = dbo_CltDueCustom.CDCustCDId
WHERE dbo_CltDue.CDID=1000 AND CltDueCustom.CDCustId=111

But that won't work for CDID = 1001. What's the best way to do it?

 

[JonFer]

Use a crosstab query and join all 3 tables on Primary keys with...

- FirmCustDef.FCustDesc as the ColumnHeading
- Min(dbo_CltDueCustom.CDCustValue) as the Value
- other fields as RowHeadings

 

[PHV]

What about this ?

SELECT P.CDID AS ProjectID, P.CDDescription AS ProjectDescription, V.CDCustValue AS Extended
FROM dbo_CltDue P LEFT JOIN (
  SELECT CDCustCDID, CDCustValue FROM dbo_CltDueCustom WHERE CDCustId=111
) V ON P.CDID = V.CDCustCDId

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[guitarzan]

PHV, that worked perfectly, THANK YOU...

JonFer: I will also look into your suggestion at a later time, thank you as well.