Trouble getting a left-join to work.

[SP]

I have two tables, A & B. Table B has adjustment factors of three
types, "COMM" being one of them. I want a query that generates all
rows of table A, and those where certain fields match, I want the
value of A.PD01 to be multiplied by the the "COMM" factor.

I have the following SQL, but it only generates the records where the
fields match, not all of them.

SELECT A.DV, A.AR, A.BU, A.MCLASS, A.CLASS, ![PD01]*[A]![PD01] AS
PD01
FROM A LEFT JOIN B ON (A.AR = .AR) AND (A.DV = .DV) AND (A.BU =
.BU) AND (A.MCLASS = .MCL)
WHERE ((![NAME]="COMM"));

 

[Smallweed]

It's your Where clause thats undermining the left join - records in A that
have no matching records in B will show Null for ![NAME] and are therefore
being left out of the list.