LEFT OUTER join issue 1

[endoflux]

Folks--

Background: I have an Access source table that's an archive of projects. Since each project may last more than 1 month, I have a field concatonated at archive time that specifies the date for that record.

In my Crystal 9 report, I have a need to compare hours completed from one month to another. To do this, I've added two instances of the same table and joined them LEFT OUTER = on an "Identifier" field.

Problem: If a project is new in the current month, the record is being excluded because there is no match in the 'last month' table.

Question: How do I join these so that I get ALL records from one table, and only the records from the 2nd table which match?

Current SQL:


SELECT [fields]
FROM `ThisMonth` `ThisMonth` LEFT OUTER JOIN `LastMonth` `LastMonth` ON `ThisMonth`.`ID`=`LastMonth`.`ID`

 

[lbass]

What you are doing should work--unless you have selection criteria on the lastmonth table.

-LB

 

[endoflux]

That was it...I had criteria left over from an earlier design attempt. Thanks!