Table join problem

[DBergkamp10]

Hi There,

This is driving me mad!

I have three tables.

1. BCustomer that I want all the records (28 in total) for a company called Hasty Market.

2. BCallsumm

3. BCallDetail

When an order is done for a product, the customer number gets entered in all three tables. Qty ordered, Ord Number etc.

Some of our products have not been ordered for all of the customers, consequently, when I specify a particular product, say product code `ABC`, it is not showing all 28 customers. For product 'ABC' 5 out of the 28 are not showing up, I'm only getting 23.

Is it a problem with my join? Or do I need a sub report?

I though if I did a left outer join, it would pull all of the records out of BCustomer regardless if it had any records in the other two tables?

I am using Crystal XI, ODBC to MS SQL 2008

Any assistance would be greatly appreciated!

Cheers!

 

[fisheromacse]

I would think that it would work using Left Outer Joins.

You will not be able to use any selection criteria on any except the leftmost (master) table.

You probably need to add the product table as the leftmost table, then the customer, callsumm and calldetail tables.

 

[jmd0252]

let me ask this,, when you ask for certain products, are the products listed for all the customers. Do the sum and the detail, have say, an order number, where you have a one to many,, like a header and detail record. That would be a left outer. Then you could join the customer file to the header, because you cannot enter a header record, without a customer entry. If youare still have "seeing", the data,, or you do not understand what is happening,, trying looking at each table by itself,, so you can understand what is happening. or join the summ and detail togather,, then add the sustomer,, and see what happens.

 

[DBergkamp10]

Yes - it is just like an invoice header and the detail table are the line items. Thx guys. I think I'll try using the header table I.e. the CallSumm table and left outer that one to the Customer table. That or I'll use a subreport.