Total time

[rae]

I have the following query:
SELECT Table1.Program, Table1.worker,
HoursAndMinutes(Sum(Table1.checkout-Table1.checkin)) AS Total_Time_Spent,
Table1.Appt_Date
FROM Table1
GROUP BY Table1.worker, Table1.Appt_Date, Table1.Program
HAVING (((Table1.Appt_Date)>=DateAdd("d",-120,Date())));

This works fine, however what I really want to do is see the total time
spent per person on each project over the space of a month. What I get is the
total time spent on each project each day.

Program worker Total_Time_Spent Appt_Date
WIA Davxxx 0:15 8/31/2006
WIA Davxxx 0:30 10/14/2006
TANF Fulxxx 0:15 11/6/2006

I don't care if there is an Appt_Date field, however, because I use it to
define the dates, I need it in the script...yes? I don't want to see the
worker name repeated unless they are working on several projects.
What could I be doing wrong?

Rae

 

[Damian S]

Hi rae,

Don't return the Table1.Appt_Date field, as this will group by appointment
date, hence the multiple lines per sales rep.

SELECT Table1.Program, Table1.worker,
HoursAndMinutes(Sum(Table1.checkout-Table1.checkin)) AS Total_Time_Spent
FROM Table1
WHERE (((Table1.Appt_Date)>=DateAdd("d",-120,Date())))
GROUP BY Table1.worker, Table1.Program;

Damian.

 

[rae]

Damian
Thanks, I think this has done the trick.
Rae