Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2013
    Posts
    163

    Unanswered: Split the Monthly values equally based on calendar

    Dear Members,

    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
    R. Vadivelan

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,423
    Provided Answers: 8
    He this should get you thinking

    I would write some function and you just have to pass the right data in it

    Code:
    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
    Paste it into a module

    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")

    to

    NumberOfweeksInThisMonth = "WK" & FORMAT(DateDiff("ww", startdate, indate),"00") & "-" & format(indate,"mmm YYYY")

    should return WK01-Jan 2015

    what happen

    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 23:36. Reason: spelling
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    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
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    sorry
    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

    Code:
    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
    you could just as easily use an end on datetime, but don't be tempted to use both. defacto the start of the next period is the end of the previous period. if you store both then you are creating a situation where dates can disappear and or overlap. yes you can code in your form to validate this, but int he Access /JET wordl you cannot impose that sort of checking at table level, where it actually matters. yes you probably could use stored procedures but there is a limit to what a non specialist can use.

    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 stuff
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •