Finding the sum of hours durning a Day

[joe31709]

Sounds easy right... But i am writing a query that need to have either the sum of a day or the sum of a week.

here is the problems sometimes a day might have more than one entry.

DATE REG HRS OVER TIME Task (SCC)
3/1/2004 8 2.5 1
3/1/2004 8 3 1
3/1/2004 8 3 1
3/1/2004 8 2.5
3/1/2004 8 3
3/1/2004 8 3
3/1/2004 8 2.5
3/4/2004 8 1
3/4/2004 8 1

Now the task code might be different also. but what I would like is that you can enter a date and it tell you the total hours (reg hrs + overtime. for that task code for that day.

Thanks for your help.
Joe

 

[doran_doran]

total query

1. you can do a total query or put a sum in your query.
sum: sum([regularTime]+[overtime])

2. dont forget to put [enter a date] under criteria for the date field.

and then click on the datasheet view. the system should prompt you for [Enter a Date], put a desire date and system should now do the sum for that date record by record.

Good Luck.
Dianna Goldsberg

 

[joe31709]

You tried to excute a query that does not include the specifed expression "date" as part of the aggerate function , this is the error message i get when i write an expression.

SELECT Time.DATE, Time.[Task (SCC)], Time.[REG HRS], Time.[OVER TIME], Sum([reg hrs]+[overtime]) AS Expr1
FROM [Time]
WHERE (((Time.DATE)=[enter date]) AND ((Time.[Task (SCC)]) Is Not Null) AND ((Time.[REG HRS]) Is Not Null) AND ((Time.[OVER TIME]) Is Not Null));

I put not null in there so i only get records with info. Now this one returns dates but if there are 2 dates , such as 2 people worked that day and had overtime i get 2 dates back, I just want to get one date back with total hours...


SELECT Time.DATE, Time.[Task (SCC)], Time.[REG HRS], Time.[OVER TIME]
FROM [Time]
WHERE (((Time.DATE)=[enter date]) AND ((Time.[Task (SCC)]) Is Not Null) AND ((Time.[REG HRS]) Is Not Null) AND ((Time.[OVER TIME]) Is Not Null));

 

[doran_doran]

why r u aggregating ??

If you are using Sum then you dont need to aggregate (meaning using the Total from the View Menu).

I just ran something similar and it worked like charm. and field name is whatever the date field is. (so if you want all the record from a date like, 3/8/04 and ur field name is FirstDate or what have you, then u going to use ur real field name) and in the criteria put [Enter a Date]

Good Luck
Dianna Goldsberg