473,889 Members | 1,818 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

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
1 2151
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(tblCheckR egister.CheckDa te) AS CheckCount
FROM tblCheckRegiste r
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 "WeekOfMont h" (Week 1 to 5)?

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

SELECT Format(([CheckDate]),"mmm") AS CheckMonth, "Week: " &
GetWeekNumberIn Month([CheckDate]) AS CheckWeek,
Count(tblCheckR egister.CheckDa te) AS CheckCount
FROM tblCheckRegiste r
GROUP BY Format(([CheckDate]),"mmm"), "Week: " &
GetWeekNumberIn Month([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.n et

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******@hotma il.com> wrote in message
news:d3******** *************** ***@posting.goo gle.com...
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
1425
by: jason | last post by:
Hi Everyone, I would really appreciate some thoughts on how best to tackle an availability calender in ASP for our yacht fleet. I need to be able to show the booking STATUS of a particular YACHT during a calender MONTH as well as its LOCATION I have attached a screenshot of an existing system that does this - see here:
24
4452
by: PromisedOyster | last post by:
Is there a way that I can get a resultset that contains unique dates in a given date range without the need to have a temporary table and a cursor? perhaps something like: declare @start_date as datetime declare @end_date as datetime set @start_date as '1/1/2005' set @end_date as '1/1/2006'
4
1744
by: Alicia | last post by:
I am having a problem grouping by week. I am looking for the simpliest way of doing it in Microsoft Access. I have tried to use a pre-loaded calender, access did not like it at all. If there is another way of counting the number of ProblemCounts with a single week, by using DatePart etc. Give me a shout. Thanks. Something similar to Before:
0
1232
by: ambelkar | last post by:
hi, I trying to join weekly & daily tables by using end_dte,Rpt_dte respectively.My week is saturday to friday.first table only having week end' dates but the 2 daily table having daily dates .when I trying to join 2 tables using the following condition weekly.end_dte=Next_day(daily.rpt_dte,'FRI') only for this friday's date it displays next friday's date. for ex: 7th is friday when I give next_day('04-05-2007', 'fri') , it should...
7
4115
by: Lucas_london via AccessMonster.com | last post by:
Hi I have set up a database/tables in Access based on daily timeseries data. However I would like to create two additional columns in the table as a basis to pull the data on a weekly and monthly series basis. Currently, in excel I am using the weekday and EOMONTH functions to create the flags/indicator in my excel dataset: (Cells in column A contains daily dates)
0
950
by: hiha | last post by:
Hi ! I wanted to change color of dates or encircle them when they are being selected in builtin control MonthCalender in vb2005. I could only make them see in bold font when we select the particular date in the event DateSelected Please can any one help me with changing the color of dates ... am badly stucked there.... Or do i have to create custom calender for serving the purpose? hiha
1
2953
by: creative1 | last post by:
Hi Everyone, I need help in one more thing. I my report that I want to print under follwoing criteria: Weekly ---------------- Set start and end date automatically to last week Bi-Weekly --------------Set start and end dates to last 15 days Monthly----------------Set date to first and last day of last month I am not using dtepicker. I have code for monthly statement EndingDate_msk.Text = Format$(DateSerial(Year(Now),...
7
2379
by: gubbachchi | last post by:
Hi all, In my application I need to display the data fetched from mysql database after the user selects date from javascript calender. I have written the code in which after the user selects the date from calender and clicks search button it will fetch data from database. But what I need is as soon the user clicks the date, it should fetch data for that date from database. How to do it. Here is the code I am using <html> <script...
1
3469
by: swethak | last post by:
hi, i have a code to disply the calendar and add events to that. It works fine.But my requirement is to i have to disply a weekly and daily calendar.Any body plz suggest that what modifications i have to made in my code <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type"...
0
9810
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11199
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10791
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10894
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10442
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7997
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7150
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5830
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
6029
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.