Sum of several time fields relating to each job number (confused yet?)

[Sontec]

This is going to be a hard one to explain...

We need a formula to total the amount of time spent on a repair job. Our database contains sevearal lines for each job (with distinct job number), each containing a period of time where the engineer has done something with the unit. What we need to end up with is a line for each job number and the total of all the periods of time spent on a job. Is this possible?

The job number field is Timelog.JOB_REF and the time field is Timelog.TIME_TAKEN.

I have spoken to the company that designed our software and they (helpfully?) sent me an SQL script to do it...maybe that could be of use to one of you lovely people

Select sum(Time_taken) from TimeLog
join Instruct on REf_code = job_ref
where job_ref = @jobnumber

Makes perfect sense to me. (not).

 

[Madawc]

For Crystal 8.5, I'd group by job number. Have Timelog.TIME_TAKEN on your detail line.

Right-click on Timelog.TIME_TAKEN and chose [Insert]. This gives you a choice of methods of totalling etc. I'd advise [Subtotal], which is the simplest.

I am assuming you want a simple total of Timelog.TIME_TAKEN and that it is a simple number. If you want to turn minutes into hours or whatever, create a formula field with divide, remainder etc.

Madawc Williams
East Anglia
Great Britain

 

[Madawc]

PS, the SQL formula may well be correct, but the big merit of Crystal is that you can get the same effects without knowing all the intricacies of the language.

Madawc Williams
East Anglia
Great Britain

 

[Sontec]

Hey! A fellow East Anglian D

Don't you just hate it when theres a really simple answer that you did'nt think of yourself?

Thanks alot for your help..much appreciated.