join multiple parameters 1

[evaleah]

I have an SQL statement that works in SQL Server and Oracle when written this way:

SELECT
INTERPRETATIONVALUES.CODEID,
Count(CODESYSTEMVALUES.CODESYSTEMVALUEID) AS ExistingValues,
DATASPECIFICATIONS.SHORTNAME,
DATASPECIFICATIONS.LONGNAME,
INTERPRETATIONVALUES.HARVESTTEXT
FROM INTERPRETATIONVALUES
INNER JOIN DATASPECIFICATIONS ON INTERPRETATIONVALUES.SHORTNAME = DATASPECIFICATIONS.SHORTNAME
LEFT JOIN CODESYSTEMVALUES ON INTERPRETATIONVALUES.CODEID = CODESYSTEMVALUES.IVCODEID [b]and CODESYSTEMVALUES.CODEID = 5[/b]
GROUP BY INTERPRETATIONVALUES.CODEID, DATASPECIFICATIONS.LONGNAME, INTERPRETATIONVALUES.HARVESTTEXT, DATASPECIFICATIONS.SHORTNAME, INTERPRETATIONVALUES.SHORTNAME, DATASPECIFICATIONS.SHORTNAME, CODESYSTEMVALUES.CODESYSTEMID HAVING (((CODESYSTEMVALUES.CODESYSTEMID) Is Null)) OR (((CODESYSTEMVALUES.CODESYSTEMID)= 5)) ORDER BY INTERPRETATIONVALUES.SHORTNAME;

How do I do a join like this in Access? I have tried HAVING statements but when I add
HAVING CODESYSTEMVALUES.CODEID = 5
I get only the records where the codeid = 5 and lose the left join. In other words, I want ALL records from INTERPRETATIONVALUES regardless and only the records from CODESYSTEMVALUES where CODEID = 5.

Thanks!
Eva

 

[PHV]

SELECT I.CODEID,Count(C.CODESYSTEMVALUEID) AS ExistingValues,D.SHORTNAME,D.LONGNAME,I.HARVESTTEXT
FROM (INTERPRETATIONVALUES I
INNER JOIN DATASPECIFICATIONS D ON I.SHORTNAME=D.SHORTNAME)
LEFT JOIN (SELECT IVCODEID,CODESYSTEMVALUEID,CODESYSTEMID FROM CODESYSTEMVALUES WHERE CODEID=5
) C ON I.CODEID=C.IVCODEID
GROUP BY I.CODEID,D.LONGNAME,I.HARVESTTEXT,D.SHORTNAME,C.CODESYSTEMID
ORDER BY D.SHORTNAME;

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