Total Values with mutiple links to the same table 1

[Renasant]

I have an Access database that tracks all loans secured by marketable securities. I have a five fields in the client table that are look up fields that refer to the stocks table. Access created multiple shadow copies of the stocks database in relationships. I set the joins to outer joins with the client table as the primary.

I have an crystal report to track the values, but can only get the first field to be included in the total. The second and subsequent stocks do not get calculated.

I have the stocks table in multiple times to match the fields in clients. I have outer joins with clients as primary.

My formula is:
if{Margin_Clients.Stock 1 Symbol}<>0 then
{Margin_Clients.Stock 1 # Shares} * {Stocks.Price} +
{Margin_Clients.Stock 2 # Shares} * {Stocks.Price} +
{Margin_Clients.Stock 3 # Shares} * {Stocks.Price} +
{Margin_Clients.Stock 4 # Shares} * {Stocks.Price} +
{Margin_Clients.Stock 5 # Shares} * {Stocks.Price}
else 0


If try to show the price as (stocks_1.price), stocks_2.price) etc I get an error.

 

[lbass]

Are you saying you are getting an error message? If so, what is it?

Since not everyone will have all stocks, you need to check for nulls, so change your formula to:

if{Margin_Clients.Stock 1 Symbol} <> 0 then
(
if isnull({Margin_Clients.Stock 1 # Shares}) then 0 else
{Margin_Clients.Stock 1 # Shares} * {Stocks.Price}
) +
(
if isnull({Margin_Clients.Stock 2 # Shares}) then 0 else
{Margin_Clients.Stock 2 # Shares} * {Stocks.Price}
) + //etc.

You will probably get duplicate values, however. If so, show some sample results at the detail level, and then show what the result should be for that sample.

-LB

 

[Renasant]

I have set the defalut value in the database to 0 to eliminate the issue of nulls.

This formula worked when we were using Access 2007 and crystal 10. somewhere or another in broke, probably when we went to Access 2007, however, the database is NOT 2007 format. I have not converted it.

 

[lbass]

What is the error you are getting then? Please be specific.

-LB

 

[Renasant]

I get errors when I adjust the join types. "join type not supported" and "if tables are already linked join type cannot be changed" I do not get an error when run the formula with the standard join. It just stops at the first stock.

 

[lbass]

Are you adding the tables directly into Crystal or are you linking them in Access? If you start fresh with CR, adding the tables directly into the database expert and then linking, do you have the option of using left joins?

-LB

 

[Renasant]

Yes. I am workign off of a new report. By left do you mean the client table as left or primary and the stock table as right or secondary?

 

[lbass]

You should be linking FROM the client table TO each of the five stock tables (using aliases) with a left outer join.

-LB

 

[Renasant]

I just redefined the joins that way. I changed the formula to:
{Margin_Clients.Stock 1 # Shares} * {Stocks.Price} +
{Margin_Clients.Stock 2 # Shares} * {Stocks_1.Price} +
{Margin_Clients.Stock 3 # Shares} * {Stocks_2.Price} +
{Margin_Clients.Stock 4 # Shares} * {Stocks_3.Price} +
{Margin_Clients.Stock 5 # Shares} * {Stocks_4.Price}
with the alias table name and price and
IT WORKS. Thanks for the tip on starting over.

 

[Renasant]

Is there a way to adjust the links and formula in existing reports rather than starting over? I get the error "If tables are already linked then the join type cannot change" when I try to adjust the joins and edit the formula.

Thanks

 

[lbass]

Please explain the changes you are trying to make and why.

-LB

 

[Renasant]

I am trying to change the joins in the existing reports from inner joins to outer joins and get the error "If tables are already linked then the join type cannot change" when I refresh the report. The fix works in the newly created report, but I cannot change the existing reports for the joins and the formula.

 

[lbass]

If you are linking in Access, then it sounds like you need to change the links in Access.

-LB