Select query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi, can anyone please help??

I have a database table which logs timesheets of staff. The fields are Date, Customer ID, Customer, Time spent- Staff member A, Time spent- Staff member B, Time spent- Staff member C.

I want to create a query that returns the total time spent by all staff members on each customer for a specified month in a specified year, e.g. 03-2000. However, the date field in the table stores the date as a short date format, i.e. dd/mm/yyyy. Can anyone tell me how to do this?? Any help is very appreciated!!

Bigweed
 
Something like the following. Replace the field names with your field names.

SELECT Format([Date],"mm-yyyy") as TheMonth, CustomerID,
SUM(nz(TimeSpentA,0)+ Nz(TimeSpentB,0) +NZ(TimeSpentC,0))
FROM YourTable
WHERE Format([Date]) = "03-2000"
GROUP BY Format([Date],"mm-yyyy"), CustomerID

IS time spent a datetime field? If so, then you may need to make more changes
to the above. If the total time goes over 24 hours you may seem some strange
results.
 
Back
Top