linking tables..help

[back2tek]

Hi all:
I am using Cr9 and I am having difficulties with this scenario:

Employee table (empid,fname,lname)
Sales table (empid,sales_date,amount)


For each employee in employee table I would like to list sales amount by date,
whether the employee exist in the sales table for that day or not..

Somthing like

Date 04/01/2006
John smith 100$
Peter Black ---------> has no sales that day
King Kong ---------> has no sales that day
Gladiator 50$
etc

Date 04/02/2006
John smith ---------> has no sales that day
Peter Black 300$
King Kong 20$
Gladiator ---------> has no sales that day
etc

Appreciate your help

back2tek

 

[Turkbear]

Hi,
Use a Left-Outer Join between
Employee -> Sales
using the empid field to link them..

This will return all records in the Employee table even if no matching record exists in Sales
However, do not use any selection criteria ( like a Date range) that requires the Sales table..That will 'convert' your Left-Outer join to an Equi-Join and that will not work for your needs..












To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[back2tek]

This is not what I want. I need all the employees to be listed on every day as in my sample data.
the left outer join will read the employee table one time and link it to the sales table.
How do I list them again on day 2 and 3 and so on...

 

[Madawc]

Try a Crosstab. Insert>Crosstab. That will show employees who sold something in the period, with zero for zero days.

If they might have had zero for the whole period, but you still want to show them, you would need a 'Mock Crosstab'. Use 'Search' to get details, or look up your Crystal sample reports.

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