Link problems

[Bennie47250]

Using Crystal Reports version 7.0 against an Access 97 database.

In Crystal I have joined the sales table to the product family table with an = join. Simple enough, however I’m receiving records from the sales table that do not have a match in the product family table. I have played around with the different join types and can not get the values to stop appearing in the report.

I have been able to exclude the values by excluding them in the select expert but don’t want to do it this way in the event additional items are added to the sales table that are not in the product table.

As a test I created a new quick and dirty report and used the same tables and the same types of links, it does not return these values.

Any thoughts on how to correct? I do not want to recreate the original report from scratch.

Thanks

 

[synapsevampire]

Consider the Product Family table the Parent, and the Sales table the child (drag the link from the Family to the Sales).

Use a Left Outer join.

Now you'll only get sales only where they have a Product Family.

Sounds like some bad data, why would you have sales without a product family?

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Bennie47250]

At my company, not all sales are sales. There are several items that a customer will purchase but they will not count towards the sales for the commissioned rep. For example if the customer purchases a catalog, the sales rep does not receive commission on it nor is it counted as a sale.

What I have found is the craziest thing, or it seems that way to me. If I do an = join to the 2 tables I will get sales that are not in the product family. However, if I include the joined field from the product family I do not get the sales. Doesn’t seem like it should work this way but it is.

Anyone have any thoughts on this?

Thanks

 

[synapsevampire]

I've no idea what you mean, perhaps you should share some example data and structures.

If the database is even poor by design, you can easily indicate which items do or do not receive a commision.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com