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

[meghdut]

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.

[Gruff]

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.

[meghdut]

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.

[meghdut]

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

[westconn1]

thank you for posting your solution, it may well others who find this by searching