Need Help with counting weekly dates. One more time.

[Alicia]

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:

Date ProblemCount
--------- ---------
10/02/04 2
10/04/04 4
10/08/04 3
10/16/04 1
11/02/04 3
11/03/04 1

AFTER:

Week Of ProblemCount
--------- ----------
10/07/04 6
10/14/04 3
10/21/04 1
10/28/04 0
11/04/04 4

[PC Datasheet]

First, change the name of the "Date" field to anpther name. "Date" is a reserved
word in Access and using it will give you problems!

Create a query and put the following expression in the first field:
WeekNum:Format([NameOfDateField],"ww")
Put ProblemCount in second field.

Change the query to a totals query by clicking the Sigma button on the toolbar
at the top of the screen. (Looks like a capital "E"). Under ProblemCounts,
change Group By to Count. Run the query.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"Alicia" <al******@hotmail.com> wrote in message
news:d3**************************@posting.google.c om...

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:

Date ProblemCount
--------- ---------
10/02/04 2
10/04/04 4
10/08/04 3
10/16/04 1
11/02/04 3
11/03/04 1

AFTER:

Week Of ProblemCount
--------- ----------
10/07/04 6
10/14/04 3
10/21/04 1
10/28/04 0
11/04/04 4

[Pieter Linden]

al******@hotmail.com (Alicia) wrote in message news:<d3**************************@posting.google. com>...

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.

simplest way - let Access do it in a report. Group by the date, then
choose week, or do it in a query and do something like this...

SELECT tblVouchers.DateCreated,
DateSerial(Year([datecreated]),1,Day([datecreated])) AS WeekNo,
DateDiff("ww",[weekno],[datecreated])+1 AS Expr1
FROM tblVouchers;

[Pieter Linden]

al******@hotmail.com (Alicia) wrote in message news:<d3**************************@posting.google. com>...

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.

simplest way - let Access do it in a report. Group by the date, then
choose week, or do it in a query and do something like this...

SELECT tblVouchers.DateCreated,
DateSerial(Year([datecreated]),1,Day([datecreated])) AS WeekNo,
DateDiff("ww",[weekno],[datecreated])+1 AS Expr1
FROM tblVouchers;

[Alicia]

pi********@hotmail.com (Pieter Linden) wrote in message news:<bf*************************@posting.google.c om>...

al******@hotmail.com (Alicia) wrote in message news:<d3**************************@posting.google. com>...

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.

simplest way - let Access do it in a report. Group by the date, then
choose week, or do it in a query and do something like this...

SELECT tblVouchers.DateCreated,
DateSerial(Year([datecreated]),1,Day([datecreated])) AS WeekNo,
DateDiff("ww",[weekno],[datecreated])+1 AS Expr1
FROM tblVouchers;

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.