Results 1 to 4 of 4

Thread: Week Mapping

  1. #1
    Join Date
    Aug 2002
    Location
    Singapore
    Posts
    91

    Unanswered: Week Mapping

    I try to map month to week...
    I already have a table that store date of the first day of the first week of each month (or user may choose other first day of the week).

    This is the sample for year 2002 (first day of week is Mon):
    Code:
    Month  Date
    1      7/01/02
    2      4/02/02
    3      4/03/02
    4      1/04/02
    ...
    Then i query for number of week, by substracting the next month date with the current date and divide it by 7, and the result as follow:

    Code:
    Month NumOfWeek
    1      4
    2      4
    3      4
    ...
    Now... I want to generate something like this:

    Code:
    Month WeekNumber
    1       1
    1       2
    1       3
    1       4
    2       5
    2       6
    2       7
    2       8
    3       9
    3      10
    3      11
    3      12
    ...
    How to write query to produce this result? Although I can just directly store it on a table on this form... but, I still want to store the date as this will give the exact date. Can this be generated from query?

  2. #2
    Join Date
    Sep 2002
    Location
    Havre de Grace, Maryland, USA
    Posts
    8
    Function VBAWeekNum(D As Date, FW As Integer) As Integer
    VBAWeekNum = CInt(Format(D, "ww", , FW))
    End Function


    The FW parameter: Set it to 1 to indicate that weeks begin on Sunday, or to 2 to indicate that weeks begin on Monday.

  3. #3
    Join Date
    Jul 2002
    Location
    Romania
    Posts
    122
    I don't think you need the first date of each week...
    I suppose the following query will return what you need:

    Select Month(YourDateField) As Mth, DatePart("ww", YourDateField) As Wk From YourTable Group By Month(YourDateField), DatePart("ww", YourDateField);

    If you don't need grouping, just delete the blue text.

    HTH

    Dan

  4. #4
    Join Date
    Aug 2002
    Location
    Singapore
    Posts
    91
    No...
    The user want to group the week differently. For some reason, they sometimes change the policy from time to time. For example, last year maybe we can just group using your formula. But this year and next year, their data already shows the weeks grouping differently. Of course I must allow user to customize the week grouping...

    For jcole, I think VBAWeekNum = CInt(Format(D, "ww", , FW)) only return 1 value. Actually I want 4 or 5 values... (as I dont want redundant data, I only store one D each month... and now I want to generate about 4 or 5 week number per month...)

Posting Permissions

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