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

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

P: n/a
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.
Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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.

Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.