471,073 Members | 1,496 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,073 software developers and data experts.

Date Query Grouping Help!

Yes, but will that skip a week and group by the date for me? 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
5 2177
al******@hotmail.com (Alicia) wrote in message news:<d3**************************@posting.google. com>...
Yes, but will that skip a week and group by the date for me? 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.


If you had a function to convert the date into a week starting date
then something like:

SELECT FirstDayOfWeek([ResourceDate]) As FirstDate, Sum(Total) As
WeekSum FROM tblResourceDateTotals GROUP BY FirstDate;

would give you what you need.

So after a Google search:

From Peter Duerden (pd******@pjdsystems.freeserve.co.uk):

DateAdd("d",(2-(Weekday([ResourceDate]))),[ResourceDate]) gives the
first day of the week containing ResourceDate.

SELECT DateAdd("d",(2-(Weekday([ResourceDate]))),[ResourceDate]) As
FirstDate, Sum(Total) As WeekSum FROM tblResourceDateTotals GROUP BY
DateAdd("d",(2-(Weekday([ResourceDate]))),[ResourceDate]);

Gave me: 3/31/2003 6
4/7/2003 8

So it looks like you need the last day of the week to define which
week is which (so I add 7 to the 2 and rename).

SELECT DateAdd("d",(9-(Weekday([ResourceDate]))),[ResourceDate]) As
LastDayOfWeek, Sum(Total) As WeekSum FROM tblResourceDateTotals GROUP
BY DateAdd("d",(9-(Weekday([ResourceDate]))),[ResourceDate]);

This finally gave me the results you asked for. I have not tested
this any further than your sample data. Maybe someone can suggest a
way to streamline this query further. Also note that I removed the
space from the [Resource Date] field. You also need to change the
tblResourceDateTotals in the query to the actual name you used for
your BEFORE table/query.

James A. Fortune
Nov 13 '05 #2
Thanks James, would you mind sending me the MDB Access file dealing
with my sample data, Access is giving me a problem.
al******@hotmail.com
Nov 13 '05 #3
Thanks James, would you mind sending me the MDB Access file dealing
with my sample data, Access is giving me a problem.
al******@hotmail.com

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #4
Alicia Roberts <al******@hotmail.com> wrote in message news:<40**********************@news.newsgroups.ws> ...
Thanks James, would you mind sending me the MDB Access file dealing
with my sample data, Access is giving me a problem.
al******@hotmail.com

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!


It's on its way.

James A. Fortune
Nov 13 '05 #5
A much simpler system for week numbers.

StartDay = Date of day before First day of first week.

WeekNo: Int(DateDiff("d",StartDay,Now())/7)

The first week will be "0". Just add "1" to the result for first
week to equal "1".

Just use View/Totals "Count" of "resource dates" for the first
report and "Count" of "WeekNo" for the second report.
Alicia wrote:
Yes, but will that skip a week and group by the date for me? 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 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Debbie Davis | last post: by
7 posts views Thread by jane | last post: by
5 posts views Thread by Bayla Frankl | last post: by
1 post views Thread by Matt | last post: by
reply views Thread by leo001 | last post: by

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.