Select query

[Bigweed]

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

 

[John Spencer (MVP)]

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.