How to list an employee name with 0 as a total

[ynott]

I'll make this simple. I have an Access Database that list's every employee's number (Personnel.EmpNo)

I link the Access document to an Oracle DB via Personnel.EmpNo <-> Sales.Emp_No

In that same Sales table, there is a field called Sales.Ticket, which is a unique number. Then, I do a count and suppress all but the count and group name. Here's the output

EmpNo # of Sales
002 16
004 10
005 2

Now, if an employee doesn't have a sell that month, the employee number is not listed on the report at all.

My goal is to list the employee number and state that the total is 0. How can this be acheived?

TIA

 

[synapsevampire]

Ratehr than do the join within Crystal, add the oracle table as a LINKed table (not import) to the Access database, and then build out the query within Access as the source for the Crystal Report.

You'll find the ODBC data sources listed in the file types at the bottom, and Access will be much faster than Crystal, plus you can do a LEFT OUTER join in Access, so you will get all of the names.

-k

 

[ynott]

Unfortunately, I simplied what I'm doing. The report is much more complicated than just what I'm showing. I using the charts within Crystal to also perform division break-downs. If it can be done it Crystal, I'm hoping to do that. Thanks for the idea though.

 

[Madawc]

You need a left-outer between Sales and Sales.Ticket. And this will only work if you don't add a record selection for Sales.Ticket.

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

 

[ynott]

Thanks, but how do I narrow down the Sales.date between two dates then?

 

[lbass]

Remove any criteria on the sales table, and then create a conditional formula like:

if isnull({table.salesID}) or
not({table.salesdate} in date(2006,11,1) to date(2006,11,30)) then 0 else
{table.sales}

Then right click on this formula and insert a summary at the group level. You can parameterize the dates as necessary.

-LB