By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,191 Members | 1,256 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,191 IT Pros & Developers. It's quick & easy.

Need help on Grouping by Week or DatePart function in ACCESS?

P: n/a
Hi, I am trying to group by week and do a count of my data in between
the dates. In Microsoft Access.

Something similar to

Before:

Date Count
--------- ---------
10/02/04 2
10/04/04 4
10/08/04 3
10/16/04 1
11/02/04 3
11/03/04 1

AFTER:

Week Of Count
--------- ----------
10/07/04 6
10/14/04 3
10/21/04 1
10/28/04 0
11/04/04 4

So I would like to start with the first week according to the earliest
date in the query. And just increase by 7 days, counting all the
values from the days. I tried to use DatePart('ww',date) , it didn't
work, spits out the wrong dates. Thanks in advance.
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
al******@hotmail.com (Alicia) wrote in
news:d3**************************@posting.google.c om:
Hi, I am trying to group by week and do a count of my data in
between the dates. In Microsoft Access.

Something similar to

Before:

Date Count
--------- ---------
10/02/04 2
10/04/04 4
10/08/04 3
10/16/04 1
11/02/04 3
11/03/04 1

AFTER:

Week Of Count
--------- ----------
10/07/04 6
10/14/04 3
10/21/04 1
10/28/04 0
11/04/04 4

So I would like to start with the first week according to the
earliest date in the query. And just increase by 7 days,
counting all the values from the days. I tried to use
DatePart('ww',date) , it didn't work, spits out the wrong
dates. Thanks in advance.


Create a function that returns the weekending date, then call
that in the query.

Public function WEDate(Actual as variant) as variant.
if isdate(actual) then
WEDate = dateadd("d",actual,7-weekday(actual))
end if
end sub

But that will not return the row 10/28/04 0
For that you will need to create a calendar table and right join
that to your existing data.

Bob Quintal
Nov 13 '05 #2

P: n/a
"Alicia" <al******@hotmail.com> wrote in message
news:d3**************************@posting.google.c om...
Hi, I am trying to group by week and do a count of my data in between
the dates. In Microsoft Access.

Something similar to

Before:

Date Count
--------- ---------
10/02/04 2
10/04/04 4
10/08/04 3
10/16/04 1
11/02/04 3
11/03/04 1

AFTER:

Week Of Count
--------- ----------
10/07/04 6
10/14/04 3
10/21/04 1
10/28/04 0
11/04/04 4

One way to solve these problems is by using a pre-loaded calendar table to
hold the temporal information with the groupings that you need.

create table weeks
(
weekstart datetime not null primary key,
weekend datetime not null
)

insert into weeks(weekstart, weekend) values (#09/30/04#, #10/07/04#)
insert into weeks(weekstart, weekend) values (#10/07/04#, #10/14/04#)
insert into weeks(weekstart, weekend) values (#10/14/04#, #10/21/04#)
insert into weeks(weekstart, weekend) values (#10/21/04#, #10/28/04#)
insert into weeks(weekstart, weekend) values (#10/28/04#, #11/04/04#)

select w.weekstart, sum(a.[Count]) as newCount
from weeks as w
left outer join alicia as a on w.weekstart > a.[Date]
and w.weekend <= a.{Date]
group by w.weekstart



Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.