Outer join not working

[tgarner444]

I have a single file which contains all of our company customers. I want to select all of our customers who did business with us in 2004, with an indication whether they did business with us in 2006 or not.

In the Database Expert I select the customer table twice, the second being given an alias. I join the two tables on customer id, with a left outer join. For the first table I select records only with the year 2004. I select records with 2006 from the alias table.

This doesn't work. Somehow the left outer join is becoming an inner join. The only customer records selected are those we did business with in 2004 AND 2006.

Please assist.

[kerna111]

Instead of an outer join, you could try "{year field} in ['2004','2006']" in the record selection formula editor.

You could then create a formula if the customer field is a group:

if distinctcount({year field}, {customer field group})>1
then
'X'
else
''

[kerna111]

Sorry but I forgot one thing in my previous post. I would then create a year group as the 2nd group and have the data on this group and suppress it if the year='2006'. I think this should work now.

[nuruladha]

excuse me , i need your help..
how can i create a new post?? thankss...
i really need help!!

[JaganEllis]

The left join becomes an inner join because you have a where-clause restriction on the left joined table - i.e. you are specifically requesting to only show records where the left joined table has records for 2006. You actually want the restriction on the join itself, but Crystal doesn't support this.
Try using a command object, an SQL Expression or a subreport instead.