I need to create a report that will display the total number of hours a person spend working for a client for the week.the second part of the report should display the total number of hrs of sevice spend in a client per week. I have attached a sample database of my database. my e-mail is william.g.salamanca@ubs.com
I created the following query to extract the data
BillHrs WrkCdId CompName DateWork AdminName Dayvalue Wkday wknumb
example
Adminane: John Doe
Datework 9/13,9/14,9/15,9/16 and 9/17
Billhrs 5,5,5,5,5
Billhrs 3,3,3,3,3
compnane: sony and lucent
WrkCdid: research
DayValue: DatePart('w',[DateWorked])
Weekday: IIf([DayValue]=1,"Sunday",IIf([DayValue]=2,"Monday",IIf([DayValue]=3,"Tuesday",IIf([DayValue]=4,"Wednesday",IIf([DayValue]=5,"Thursday",IIf([DayValue]=6,"Friday","Saturday"))))))
weeknumber: DatePart("ww",[DateWorked],1)
Output
John Doe week of 8/30 - 10/01
8/30
compname Mon Tues Wed Thurs Fri
Lucent 5 5 5 5 5
Sony 3 3 3 3 3
9/6
Goya 8 8 8 8 8
Sony 2 2 2 2 2
9/13
Verizon 8 8 8 8 8
9/20
Duracel 8 8 8 8 8
9/27
Microsoft 8 8 8 8 8
Secont part wk1 wk2 wk3 wk4 wk5
Sony 15 10
Lucent 25
Goya 40
Verizon 40
Duracel 40
Microsoft 40
Microsoft