combining data from different tables

[spunky1]

using crystal 2008, have 2 tables, agentactivitylog and calldetail.
the agentactivitylog contains agentname, time spent in different states (on a call, meeting, training, break, etc),
the calldetail table contains agentname and time spent on calls and call state, ie, incoming or outgoing.
I have all the info I need to get out of the agentactivitylog but need to add the total
number of calls per agent and call time calculated from calldetail table. ie,
Agent Incoming calls Outgoing calls Incoming talk time Outgoing talk time Break time Training Time etc
Ralph 7 10 1:20:45 1:10:05 45:00 2:10:30
Steve 10 5 2:15:00 1:00:03 25:00 1:45:00


unable to get the info to come in correctly either using a running total or using a subreport
It doesn't seem like it should be that hard so I think I am missing something simple.

 

[Madawc]

Suppose you link table A to table B, using a shared account number. If there are 7 records on table B for a record on table A, you get seven detail lines. So if you add a value from table A for the whole report, you get a result that is much too big.

If table B is variable and may have no entries for some accounts, you will not get those accounts unless you make it a 'Left Outer' link. And if you also do a selection test on Table B, you will need first to test for null values.

The normal solution is to group by account number and to modify your totals to add just once per group, if appropriate. 'Distinct Count' is also useful in some cases.

If you're not already familiar with Crystal's automated totals, see FAQ767-6524.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP