03-09-15, 13:28 #1Registered User
- Join Date
- Oct 2013
Unanswered: Split the Monthly values equally based on calendar
I have a table in access which contains the Monthly Planned Man-hours of Employees. Al so I have a Project Calendar showing the Project dates on Weekly Basis.
Now I want to create a query in such a way that my monthly man-hours should be equally splitted into weekly values based on the number of weeks.
For ex I have 160 Hours/Month, In January I have five weeks so it should divide 160 by five and show the values in WK01 to WK05. If I have four weeks then it should be divided by 4 and values should be from WK01 to WK04.
I had done the above using manually for every months, however I want to do the same automatically since I have 36 months of data for each and every person. The Objective is to track the status of Man hours Plan vs Actual for all the employees in a Project.
I will appreciate if any body can share me a useful template to track the man hours booked by Each employee on weekly basis.Thanks and Regards
03-09-15, 21:54 #2(Making Your Life Easy)
Provided Answers: 5
- Join Date
- Feb 2004
- New Zealand
He this should get you thinking
I would write some function and you just have to pass the right data in it
Function st1month(Tdate) st1month = DateSerial(Year(Tdate), Month(Tdate), 1) End Function Function LastDay(Tdate) Lastd = DateAdd("m", 1, st1month(Tdate)) LastDay = DateAdd("d", -1, Lastd) End Function Function WeeksInMonth(indate As Date) Dim startdate As Date Dim Enddate As Date startdate = st1month(indate) NumberOfweeksInThisMonth = "WK" & FORMAT(DateDiff("ww", startdate, indate),"00") End Function Public Function WEEKEND(dat) As Date If IsNull(dat) Then Exit Function dat = DateSerial(Year(dat), Month(dat), Day(dat)) If dat Mod 7 > 0 Then WEEKEND = dat - dat Mod 7 + 7 Else WEEKEND = dat End If End Function
then in a query
MonthWK: WeeksInMonth([datefeildnameinhere]) return WEEK Number of THAT month pass in as a datevalue
it will return WK01 and WK01 for differance months
if you change
NumberOfweeksInThisMonth = "WK" & FORMAT(DateDiff("ww", startdate, indate),"00")
NumberOfweeksInThisMonth = "WK" & FORMAT(DateDiff("ww", startdate, indate),"00") & "-" & format(indate,"mmm YYYY")
should return WK01-Jan 2015
on the 30th and 31st of march and they in the march figs of in april figs
what i do is when reporting I put everything into a WEEKEND Date
so 30th and 31st march fall in on the april 4th weekend date Which then comes April figs
Last edited by myle; 03-09-15 at 22:36. Reason: spellinghope this help
See clear as mud
the aim is store once, not store multiple times
Remember... Optimize 'til you die!
Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
VB based on my own environment: vb6 sp5
ASP based on my own environment: 5.6
VB-NET based on my own environment started 2007
SQL-2005 based on my own environment started 2008
YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
03-10-15, 04:52 #3Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
all this goes back to your ealry machinations with a calendar
had you designed your calednar table the way it was suggested you can derive all this sort of stuff
a calendar table matchs up true dtaes with financial or accoutnign or production dates or whatever other dates you need
you can define whatever structure you need.
ferinstance I often have to deploy things based on accounting calendars
where youd have something like
Fyear the accounts year (so it could be 2015, or 2016 or whatever they feel comfortable with MajorPeriod an identifier usually runs from the first month in the accounting year MinorPeriod an identifier, usually the week number (depends on customer needs but that could be from wk1 to wk 52/3, OR 1 to 4/5 each month StartsOn a datetime column representing the start of this period
to find the accounting period you do a join in a query and that value can be used to do this sort of grouping
the real advantage is that there is no other code to maintain. so if reports get screwed up because of date issues is not a developer issue its the users issue because they have forgotten to sort out their data in time. there is no expectation that you as a developer have to drop everything usually at peak workload. also making changes under pressure is a sure fire way of either making a mistake (not realising the complexity of the problem or impact of the change), or putting undue pressure on workloads. |Although to be fair when developer resource is allocated to Accounting department issues its amazing how 'generous' accountign budgets are to help you pull them form out of the sticky smelly brown stuffI'd rather be riding on the Tiger 800 or the Norton