Date Query Grouping Help!

[Alicia]

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.

[James Fortune]

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

[Alicia]

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

[Alicia Roberts]

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!

[James Fortune]

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

[Peter Flick]

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.