Need to sum hours spent on a project

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

Guest

I have a database originally built for payroll. I need to sum the hours
spent by each employee on individual projects. For instance, Joe worked on
company 415 on 07/01/06 for 6 hours, company 416 for 1 hour and company 417
for 1 hour. Ray worked on company 415 for 2, 416 for 3, 417 for 2, and 418
for 1. I need the total hours spent by both employees on each company for
each day i.e., on 07/01/06, 8 hours total were worked on company 415. I will
need to expand that to weekly totals, monthly and yearly. We will then be
able to fix the budget based on the various employee's salaries.

I hope this explanation makes sense and someone in the community can help me
write a query. My access knowledge is - obviously - very limited.

Thank you,

Rioliaden
 
To offer specific query suggestions, we really need specific data fields.
It isn't clear from your description how the data is being organized/stored.

Do you actually have a table that holds:

CompanyNumber
EmployeeID
DateWorked
HoursWorked

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Yes, I have these fields in one table.

Jeff Boyce said:
To offer specific query suggestions, we really need specific data fields.
It isn't clear from your description how the data is being organized/stored.

Do you actually have a table that holds:

CompanyNumber
EmployeeID
DateWorked
HoursWorked

Regards

Jeff Boyce
Microsoft Office/Access MVP
Click to expand...
 
Hi, i'm also new to database but have done a similar thing.
this si what i did

Created a query

SELECT TimeSheet.Date, TimeSheet.[File Number], TimeSheet.Employee, TimeSheet.
Code, TimeSheet.Details, TimeSheet.Units
FROM TimeSheet
WHERE (((TimeSheet.Date)>=forms!DateEntry!txtDateFrom And (TimeSheet.Date)
<=forms!DateEntry!txtDateTo) And ((TimeSheet.Employee)=forms!DateEntry!
txtname));

txtdatefrom
txtdateto
txtname

the file num = job num
Emloyee = who did the job
Units = num of hrs

Then i created a report using this query

And created a text box to sum the units

=DSum("[myQuery]![Units] ","[My Query]","Reports![MyReport]![FileNum] =
[Myquery]![File Number]")
Or something like this.
i hope you get some ideas form it anyway. i got it working in the end.
 
Create a new query. In design mode, add the table, and add the fields you
need (sounds like CompanyNumber, DateWorked and HoursWorked.

Click the "Totals" toolbar button (the greek 'sigma' symbol).

Keep CompanyNumber and DateWorked as "GroupBy", and change HoursWorked to
"Sum".

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Back
Top