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]

Re: Date Query Grouping Help!

alic2301@hotmai l.com (Alicia) wrote in message news:<d315a95e. 0407060714.52da 9f2c@posting.go ogle.com>...[color=blue]
> 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.[/color]

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 tblResourceDate Totals GROUP BY FirstDate;

would give you what you need.

So after a Google search:

From Peter Duerden (pduerden@pjdsy stems.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 tblResourceDate Totals 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 tblResourceDate Totals 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
tblResourceDate Totals in the query to the actual name you used for
your BEFORE table/query.

James A. Fortune

[Alicia]

Re: Date Query Grouping Help!

Thanks James, would you mind sending me the MDB Access file dealing
with my sample data, Access is giving me a problem.
alic2301@hotmai l.com

[Alicia Roberts]

Re: Date Query Grouping Help!

Thanks James, would you mind sending me the MDB Access file dealing
with my sample data, Access is giving me a problem.
alic2301@hotmai l.com

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

[James Fortune]

Re: Date Query Grouping Help!

Alicia Roberts <alic2301@hotma il.com> wrote in message news:<40ed6798$ 0$16484$c397aba @news.newsgroup s.ws>...[color=blue]
> Thanks James, would you mind sending me the MDB Access file dealing
> with my sample data, Access is giving me a problem.
> alic2301@hotmai l.com
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it![/color]

It's on its way.

James A. Fortune

[Peter Flick]

Re: Date Query Grouping Help!

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:[color=blue]
> 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.[/color]