show all records in a join

[Kenyon]

I have a query that has two tables joined together. The first table has
fields:

Plant
Material Code
Material Description

The second table has fields:
Posting Date
Movement Type
Storage Location

I am trying to get it to show me the most recent posting date for all
materials by plant. I would like for it to show all materials even if they
have null values. I have spent hours trying to make the outer join work with
no luck. Can someone tell me what I am doing wrong. MY SQL is attached.
SELECT Sheet13.Plant, Sheet13.[Material Code], Sheet13.[Material
Description], Max(dbo_Material_Movement_tb.Posting_Date) AS MaxOfPosting_Date
FROM Sheet13 LEFT JOIN dbo_Material_Movement_tb ON (Sheet13.Plant =
dbo_Material_Movement_tb.Plant_Code) AND (Sheet13.[Material Code] =
dbo_Material_Movement_tb.Material_Code)
WHERE (((dbo_Material_Movement_tb.Movement_Type)="601" Or
(dbo_Material_Movement_tb.Movement_Type)="641" Or
(dbo_Material_Movement_tb.Movement_Type)="643" Or
(dbo_Material_Movement_tb.Movement_Type)="261" Or
(dbo_Material_Movement_tb.Movement_Type)="543") AND
((dbo_Material_Movement_tb.Storage_Location_Code) Is Not Null))
GROUP BY Sheet13.Plant, Sheet13.[Material Code], Sheet13.[Material
Description]
HAVING (((Sheet13.Plant)="bc") AND ((Sheet13.[Material Code])="rm408" Or
(Sheet13.[Material Code])="rx11406"));

 

[MGFoster]

I have a query that has two tables joined together. The first table has
fields:

Plant
Material Code
Material Description

The second table has fields:
Posting Date
Movement Type
Storage Location

I am trying to get it to show me the most recent posting date for all
materials by plant. I would like for it to show all materials even if they
have null values. I have spent hours trying to make the outer join work with
no luck. Can someone tell me what I am doing wrong. MY SQL is attached.
SELECT Sheet13.Plant, Sheet13.[Material Code], Sheet13.[Material
Description], Max(dbo_Material_Movement_tb.Posting_Date) AS MaxOfPosting_Date
FROM Sheet13 LEFT JOIN dbo_Material_Movement_tb ON (Sheet13.Plant =
dbo_Material_Movement_tb.Plant_Code) AND (Sheet13.[Material Code] =
dbo_Material_Movement_tb.Material_Code)
WHERE (((dbo_Material_Movement_tb.Movement_Type)="601" Or
(dbo_Material_Movement_tb.Movement_Type)="641" Or
(dbo_Material_Movement_tb.Movement_Type)="643" Or
(dbo_Material_Movement_tb.Movement_Type)="261" Or
(dbo_Material_Movement_tb.Movement_Type)="543") AND
((dbo_Material_Movement_tb.Storage_Location_Code) Is Not Null))
GROUP BY Sheet13.Plant, Sheet13.[Material Code], Sheet13.[Material
Description]
HAVING (((Sheet13.Plant)="bc") AND ((Sheet13.[Material Code])="rm408" Or
(Sheet13.[Material Code])="rx11406"));

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Try this (untested):

SELECT S.Plant, S.[Material Code], S.[Material Description],
M.Posting_Date

FROM Sheet13 As S LEFT JOIN dbo_Material_Movement_tb As M
ON S.Plant = M.Plant_Code AND S.[Material Code] = M.Material_Code

WHERE M.Posting_Date =
(SELECT MAX(Posting_Date)
FROM dbo_Material_Movement_tb
WHERE Plant_code = S.Plant AND Material_Code = S.Material_Code)

AND M.Movement_Type IN ("601", "641", "643", "261", "543")
AND M.Storage_Location_Code Is Not Null
AND S.Plant="bc"
AND S.[Material Code] IN ("rm408","rx11406")

ORDER BY S.Plant, S.[Material Code]

The subquery in the WHERE clause will return the record w/ the latest
date.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQx9PSYechKqOuFEgEQKdIgCfQXw0HBbPAF4t9ODTq5GLz+CN49gAoIus
DA58YomXmcCI6WP+itoCPgMO
=Bi6B
-----END PGP SIGNATURE-----