Left Outer Join -- Still not working after reading through forum

[gyfu]

Crystal Ver 10
Microsoft SQL Server

Guys, I really need help here, I have read through the forum and have believe that I did everything but I still could not produce the report.

2 tables with outer Join Link : Table Item and Table Acct
Report is group byItem ID and displayed in the Group footer. I use running totals in number of Item Sold and
my initial display :

ITEM ID Number of Item Sold
101 45
107 55
108 234
110 433

I then want to add another column which will use another table (Table Acct) to count the number of items taken.

I created a formula field for @Acct.ItemNo;
If isnull({Acct.ItemNo}) then 0 else {Acct.ItemNo}

The moment I create the running total field or summary field I would get the following ;

ITEM ID Number of Item Sold Number of Item Taken
107 55 3
108 234 23
110 433 12

ITEMID 45 doesn't exist in the Acct Table
I would like it to display as the following :

ITEM ID Number of Item Sold Number of Item Taken
101 45 0
107 55 3
108 234 23
110 433 12

Please advise. Thanks.

-- gyfu --

 

[paulmarr]

Are you using a left outer join to the table (Table Acct)? This should work - but the running total will be blank instead of zero.

Cheers
paulmarr

 

[gyfu]

Yes.. This is true. When I do it, I don't understand why item 101 row is eliminated? Is there anything else that I am missing?

-- gyfu --

 

[gyfu]

Just to add that my Link look something like this.

Acct.ItemNo --> Item.ItemNo.

Left Outer Join
Not Enforced
Link Type : =

thanks for any of your input.

-- gyfu --

 

[Madawc]

I've not seen this particular problem myself, but I suspect it happens because you are trying to derive a running total from the field itself.

Do a separate and distinct running total, which counts some other field, one that is always there. But use a formula to evaluate: not isnull({Acct.ItemNo}).

Right-click on a field and choose Insert to get a choice of Running Total or Summary. Or else use the Field Explorer, the icon that is a grid-like box, to add running totals.

Running totals allow you to do clever things with grouping and formulas. They also accumulate for each line, hence the name. The disadvantage is that they are working out at the same time as the Crystal report formats the line. You cannot test for their values until after the details have been printed. You can show them in the group footer but not the group header, where they will be zero if you are resetting them for each group.

Summary totals are cruder, but are based directly on the data. This means that they can be shown in the header. They can also be used to sort groups, or to suppress them. Suppress a group if it has less than three members, say. They default to 'Grand Total', but also can be for a group.


Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[kskid]

Need to reverse the links

Item.ItemNo -->LOJ--> Acct.ItemNo

item 45 will now show up with 0 in the acct totals

-lw

 

[gyfu]

I have tried using both running totals and group summary, both giving me the same results, however, it is nice to have that extra tip.

KSkid, the reverse link works well.Thanks.



-- gyfu --