[RESOLVED] how to group dates from ms access database as week of month using excel vba-VBForums
Results 1 to 5 of 5

Thread: [RESOLVED] how to group dates from ms access database as week of month using excel vba

  1. #1

    Thread Starter
    New Member
    Join Date
    May 2016
    Posts
    8

    Resolved [RESOLVED] how to group dates from ms access database as week of month using excel vba

    I am using MS access 2010 database and working with Excel VBA to connect to the database and make queries. Suppose I have a table named "MyTable" like this below:


    Date Count
    7/7/2016 12
    7/8/2016 15
    7/15/2016 18
    7/18/2016 16
    8/7/2016 15
    8/8/2016 10
    8/15/2016 9
    8/16/2016 18


    Now I want to use query to get a table like this:


    Week by Month Sum
    July Week 2 27
    July Week 3 18
    July Week 4 16
    Aug Week 2 25
    Aug Week 3 27


    Please help.
    Last edited by meghdut; Aug 28th, 2016 at 11:58 AM. Reason: grammatical mistake

  2. #2
    Frenzied Member Gruff's Avatar
    Join Date
    Jan 2014
    Location
    Scappoose Oregon USA
    Posts
    1,293

    Re: how to group dates from ms access database as week of month using excel vba

    Weeks do not fit neatly into months. You get days left over.
    Also What do you consider the first day of a week? Sunday? Monday? ...
    By your definition does week one start Jan 1 regardless of the day of the week (Partial Week)
    or by the first full week of January Starting on a given weekday?

    Once you know the answers above you can calcuate the week of the month from a date.

    In your query you would use Group By MonthWeek and use SUM(count) as Total.
    Burn the land and boil the sea
    You can't take the sky from me


    ~T

  3. #3

    Thread Starter
    New Member
    Join Date
    May 2016
    Posts
    8

    Re: how to group dates from ms access database as week of month using excel vba

    I will take the 1st day of month as the first week of that month, week starts on Sunday. So 31st July, 2016 will show up as 5th Week of July whereas in the same week, 1st August will show up as Aug 1st week. Now guide me how to achieve this one by query in access 2010.
    Last edited by meghdut; Aug 28th, 2016 at 02:30 PM.

  4. #4

    Thread Starter
    New Member
    Join Date
    May 2016
    Posts
    8

    Re: how to group dates from ms access database as week of month using excel vba

    I have solved this as below:

    select weeknum, sum(count1) from ( select format(date1,'MMM') & " Week - " & int((datepart('d',date1,1,1) -1 ) / 7 + 1) as weeknum, count1 from MyTable) group by weeknum

    Thanks everyone for your valuable time

  5. #5
    PowerPoster
    Join Date
    Dec 2004
    Posts
    22,877

    Re: [RESOLVED] how to group dates from ms access database as week of month using exce

    thank you for posting your solution, it may well others who find this by searching
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Tags for this Thread

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.