Summarizing similar fields and totaling

[morles]

I trying to create a report that will calculate the total value an engineer generates from his customer service cases.

I have four fields in my report:
Case No: Unique ID for each Customer service case
Case Fee: Value of case (currency)
Engineer1: Engineer Name
Engineer2: Engineer Name

The problem I am having is that some customer services cases are dealt by one engineer and some are dealt by two engineers. So an engineer’s name could be input in either field. I would like to find a total that each engineer generates whether their name is input in either field. Essentially what I am trying to do is create the group summaries by engineer with the total case value beside the engineer’s name. If possible I would like it to be in Basic or Crystal Syntax and not SQL (as I only have basic knowledge of it). I’m not sure if this is even possible.Any help would be really appreciated.

 

[IanWaterman]

YOu will have to use a command or view to union the data, this requires you to write some SQL on the following lines

select CaseNo, CaseFee, Engineer1, 'Eng1' as Engineer
From yourtable
where Engineer1 is not null
union all
select CaseNo, CaseFee, Engineer2, 'Eng2' as Engineer
From yourtable
where Engineer2 is not null

I have added the dummy colum Engineer so that you know which filed the data came from. Might be useful if not don't bother.

Ian

 

[morles]

Thanks so much that worked really well! The only issue now is that I need a formula to work out how much each engineer will generate. So now there are duplicates in the CaseNo field (which is fine as this only happens when there is more than one engineer dealing with the case), however if there is two engineers on a case then the the case fee is to be divided by two. So essentially if there is one engineer on the case then the case fee is X and if there are two engineers on the case then the case fee is X/2. Once I have this formula correct I can group the Engineer field by Engineer Name and perform an Autosum.

 

[IanWaterman]

Add an extra colun to query

select CaseNo, CaseFee, Engineer1, 'Eng1' as Engineer,
case when Engineer2 is not null 'Y' else 'N' end as 2nd_Eng
From yourtable
where Engineer1 is not null
union all
select CaseNo, CaseFee, Engineer2, 'Eng2' as Engineer,
case when Engineer1 is not null 'Y' else 'N' end as 2nd_Eng
From yourtable
where Engineer2 is not null

Ian

 

[morles]

Thanks so much. That worked perfectly!