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

[Alicia]

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.

[Bob Quintal]

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

[John Winterbottom]

"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