-
Aug 28th, 2016, 11:41 AM
#1
Thread Starter
New Member
[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
-
Aug 28th, 2016, 12:31 PM
#2
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
-
Aug 28th, 2016, 01:25 PM
#3
Thread Starter
New Member
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.
-
Aug 28th, 2016, 02:05 PM
#4
Thread Starter
New Member
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
-
Aug 28th, 2016, 04:21 PM
#5
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|