How do I group weekly dates, based on a Calender Table?

[Alicia]

I basically wanted something that would do a count of the dates, then
group them by their week name..

BEFORE:

Resource Date Total number of times that date Appeared
(Count)
4/3/03 4
4/4/03 2 (note 4/4/03 showed up twice)
4/9/03 1
4/10/03 7
I WANT TO CREATE THIS RESULT:

Resource Date CountbyWeek
4/7/03 6 (The first week of the month)
4/14/03 8 (second week)

I am basically doing this stuff for charting purposes, so I can create
bar graphs was my data by weeks of the month instead of everyday.

**Is this a simpler way of doing things. I created a Calender in
Excel, 1/1/2004, 1/8/2004/, 1/16/2004, 1/23/2004 etc.. until
1/6/2005. Skipping 7 days throughout the years to indicate weeks. I
imported the table into Access, and I linked the calender date table
to my dates that I would like to count. It is only displaying the
DateCOUNT for the dates that fall on the exact CalenderTable date. I
would like it to include the count of all of the dates that fall IN
BETWEEN the CalenderDate table. But I would only like the Dates from
the CalenderDate Field to display, with the correct count including
the values IN BETWEEN. If anyone could help that would be great.

[Don Leverton]

Hi Alicia,

If I've understood correctly, you want to count the occurrences of dates
within a given week number (week1 to 52)?

For example:
You want to know how many cheques (or "checks" for you Americans out
here ) <grin>
you have written by WeekNumber.

SELECT CLng(Format([CheckDate],"ww")) AS WeekNumber,
Count(tblCheckRegister.CheckDate) AS CheckCount
FROM tblCheckRegister
GROUP BY CLng(Format([CheckDate],"ww"))
ORDER BY CLng(Format([CheckDate],"ww"));
Results:
-------
WeekNumber CheckCount
1 3
2 2
3 6
4 2
5 7
6 3
....
-------------------------------------------------------------
Or ... is it that you want to return the "WeekOfMonth" (Week 1 to 5)?

If so, have a look at some code written by Kurt A. Fisher ...
(the GetWeekNumberInMonth() function in particular.)
http://groups.google.ca/groups?q=Wee...gcy.com&rnum=2

SELECT Format(([CheckDate]),"mmm") AS CheckMonth, "Week: " &
GetWeekNumberInMonth([CheckDate]) AS CheckWeek,
Count(tblCheckRegister.CheckDate) AS CheckCount
FROM tblCheckRegister
GROUP BY Format(([CheckDate]),"mmm"), "Week: " &
GetWeekNumberInMonth([CheckDate]), Month([CheckDate])
ORDER BY Month([CheckDate]);

Results:
-------
CheckMonth CheckWeek CheckCount
Jan Week: 1 3
Jan Week: 2 2
Jan Week: 3 6
Jan Week: 4 2
Feb Week: 1 7
Feb Week: 2 3
....
--
HTH,
Don
=============================
E-Mail (if you must)
My*****@Telus.net

Disclaimer:
Professional PartsPerson
Amateur Database Programmer {:o)

I'm an Access97 user, so all posted code
samples are also Access97- based
unless otherwise noted.

Do Until SinksIn = True
File/Save, <slam fingers in desk drawer>
Loop

================================


"Alicia" <al******@hotmail.com> wrote in message
news:d3**************************@posting.google.c om...

I basically wanted something that would do a count of the dates, then
group them by their week name..

BEFORE:

Resource Date Total number of times that date Appeared
(Count)
4/3/03 4
4/4/03 2 (note 4/4/03 showed up twice)
4/9/03 1
4/10/03 7
I WANT TO CREATE THIS RESULT:

Resource Date CountbyWeek
4/7/03 6 (The first week of the month)
4/14/03 8 (second week)

I am basically doing this stuff for charting purposes, so I can create
bar graphs was my data by weeks of the month instead of everyday.

**Is this a simpler way of doing things. I created a Calender in
Excel, 1/1/2004, 1/8/2004/, 1/16/2004, 1/23/2004 etc.. until
1/6/2005. Skipping 7 days throughout the years to indicate weeks. I
imported the table into Access, and I linked the calender date table
to my dates that I would like to count. It is only displaying the
DateCOUNT for the dates that fall on the exact CalenderTable date. I
would like it to include the count of all of the dates that fall IN
BETWEEN the CalenderDate table. But I would only like the Dates from
the CalenderDate Field to display, with the correct count including
the values IN BETWEEN. If anyone could help that would be great.