Need help with grouping by week

[Russell Reimann]

Hi,

I need some assistance with a query in Access 2000.

I have a single table which contains the model number of a widget and the
date that it was created. So, for example, in the first column, I would
have "Model1", "Model2", "Model3", "Model2", etc. The second column would
contain 01/17/2005, 12/28/2004, 12/29/2004, 12/07/2004, etc.

I need a query that will count, cumulatively, the number of widgets made
each week, where each week begins on a Monday. I run this query every
month. What would the SQL look like? I want the output table to have the
name of the week in one column, and the count for that week in the second.
So, for example, I would like to have output as follows:

WeekBegin NumWidgetsCreated
========================
wk120604 18
wk121304 172
wk122004 0
wk030705 95

Obviously I need to group by week, but how does Access know that a week
should begin on a Monday?

Thanks in advance,

Russ

 

[MGFoster]

Hi,

I need some assistance with a query in Access 2000.

I have a single table which contains the model number of a widget and the
date that it was created. So, for example, in the first column, I would
have "Model1", "Model2", "Model3", "Model2", etc. The second column would
contain 01/17/2005, 12/28/2004, 12/29/2004, 12/07/2004, etc.

I need a query that will count, cumulatively, the number of widgets made
each week, where each week begins on a Monday. I run this query every
month. What would the SQL look like? I want the output table to have the
name of the week in one column, and the count for that week in the second.
So, for example, I would like to have output as follows:

WeekBegin NumWidgetsCreated
========================
wk120604 18
wk121304 172
wk122004 0
wk030705 95

Obviously I need to group by week, but how does Access know that a week
should begin on a Monday?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Use DatePart(interval, date, start_of_week) to get week of the year.

PARAMETERS [Start Date?] Date, [End Date?] Date;
SELECT DatePart("ww", Created, 2) As WeekOf, Count(*) AS Widgets
FROM table_name
WHERE Created BETWEEN [Start Date?] And [End Date?]
GROUP BY DatePart("w", Created, 2)

In DatePart() the 2 = vbMonday.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQi0sXIechKqOuFEgEQILqwCg04ZlcFEi//DfysE2TQ/fSsKEWIkAoJgJ
odI8VAPh13sxe9EUKx81go1b
=GXwy
-----END PGP SIGNATURE-----

 

[Guest]

Actually, this isn't what I had in mind. Suppose in my table, I had the
following creation dates of the widgets:

DateCreated
----------------
11/02/2004
11/05/2004
12/12/2004
12/21/2004
12/27/2004
12/28/2004
01/01/2005
01/03/2005

I would like my output to be summarized as follows. (Note that the name of
the week is "WKmmddyy", where mmddyy is always a Monday.)

WEEK_BEGIN Num_Widgets_Created
----------------- -----------------------------
wk110104 2
wk110804 0
wk111504 0
wk112204 0
wk112904 0
wk120604 1
wk121304 0
wk122004 1
wk122704 3
wk010305 1

Is there a way to revise your code to accomplish this? Thanks again for
your help.

DSS_Guy

================

Hi,

I need some assistance with a query in Access 2000.

I have a single table which contains the model number of a widget and the
date that it was created. So, for example, in the first column, I would
have "Model1", "Model2", "Model3", "Model2", etc. The second column would
contain 01/17/2005, 12/28/2004, 12/29/2004, 12/07/2004, etc.

I need a query that will count, cumulatively, the number of widgets made
each week, where each week begins on a Monday. I run this query every
month. What would the SQL look like? I want the output table to have the
name of the week in one column, and the count for that week in the second.
So, for example, I would like to have output as follows:

WeekBegin NumWidgetsCreated
========================
wk120604 18
wk121304 172
wk122004 0
wk030705 95

Obviously I need to group by week, but how does Access know that a week
should begin on a Monday?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Use DatePart(interval, date, start_of_week) to get week of the year.

PARAMETERS [Start Date?] Date, [End Date?] Date;
SELECT DatePart("ww", Created, 2) As WeekOf, Count(*) AS Widgets
FROM table_name
WHERE Created BETWEEN [Start Date?] And [End Date?]
GROUP BY DatePart("w", Created, 2)

In DatePart() the 2 = vbMonday.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQi0sXIechKqOuFEgEQILqwCg04ZlcFEi//DfysE2TQ/fSsKEWIkAoJgJ
odI8VAPh13sxe9EUKx81go1b
=GXwy
-----END PGP SIGNATURE-----

 

[JohnFol]

Have a lok at thr format function, something like this

PARAMETERS [Start Date?] Date, [End Date?] Date;
SELECT format(Created, "\WKddmmyy",vbMonday ) As WeekOf, Count(*) AS Widgets
FROM table_name
WHERE Created BETWEEN [Start Date?] And [End Date?]
GROUP BY format(Created, "\WKddmmyy",vbMonday )

Actually, this isn't what I had in mind. Suppose in my table, I had the
following creation dates of the widgets:

DateCreated
----------------
11/02/2004
11/05/2004
12/12/2004
12/21/2004
12/27/2004
12/28/2004
01/01/2005
01/03/2005

I would like my output to be summarized as follows. (Note that the name of
the week is "WKmmddyy", where mmddyy is always a Monday.)

WEEK_BEGIN Num_Widgets_Created
----------------- -----------------------------
wk110104 2
wk110804 0
wk111504 0
wk112204 0
wk112904 0
wk120604 1
wk121304 0
wk122004 1
wk122704 3
wk010305 1

Is there a way to revise your code to accomplish this? Thanks again for
your help.

DSS_Guy

================

Hi,

I need some assistance with a query in Access 2000.

I have a single table which contains the model number of a widget and
the
date that it was created. So, for example, in the first column, I
would
have "Model1", "Model2", "Model3", "Model2", etc. The second column
would
contain 01/17/2005, 12/28/2004, 12/29/2004, 12/07/2004, etc.

I need a query that will count, cumulatively, the number of widgets
made
each week, where each week begins on a Monday. I run this query every
month. What would the SQL look like? I want the output table to have
the
name of the week in one column, and the count for that week in the
second.
So, for example, I would like to have output as follows:

WeekBegin NumWidgetsCreated
========================
wk120604 18
wk121304 172
wk122004 0
wk030705 95

Obviously I need to group by week, but how does Access know that a week
should begin on a Monday?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Use DatePart(interval, date, start_of_week) to get week of the year.

PARAMETERS [Start Date?] Date, [End Date?] Date;
SELECT DatePart("ww", Created, 2) As WeekOf, Count(*) AS Widgets
FROM table_name
WHERE Created BETWEEN [Start Date?] And [End Date?]
GROUP BY DatePart("w", Created, 2)

In DatePart() the 2 = vbMonday.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQi0sXIechKqOuFEgEQILqwCg04ZlcFEi//DfysE2TQ/fSsKEWIkAoJgJ
odI8VAPh13sxe9EUKx81go1b
=GXwy
-----END PGP SIGNATURE-----

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Perhaps this (Access 2002):

PARAMETERS [Start Date?] Date, [End Date?] Date;
SELECT "WK" & week_begin - (WeekDay(week_begin)-2) As WeekOf, Count(*)
AS Widgets
FROM table_name
WHERE Created BETWEEN [Start Date?] And [End Date?]
GROUP BY "WK" & week_begin - (WeekDay(week_begin)-2)

The calculation converts the date in WEEK_BEGIN into the Monday date in
which the WEEK_BEGIN date resides. But, it pushes Sundays into the
following Monday. IOW, Sunday is considered part of the week beginning
on the following Monday. If no work is done on Sunday then everything
is fine; otherwise, I'll leave the fix to that little problem to you.
Hint: IIF() function.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQi39fIechKqOuFEgEQJ4aACfSdDs6IFmTasshB6W/TMhD7KahKUAoNpL
EVAlaU+4+S9hbT8UYvTFFjV+
=XMna
-----END PGP SIGNATURE-----

Actually, this isn't what I had in mind.

I would like my output to be summarized as follows. (Note that the name of
the week is "WKmmddyy", where mmddyy is always a Monday.)

WEEK_BEGIN Num_Widgets_Created
----------------- -----------------------------
wk110104 2
wk110804 0
wk111504 0
wk112204 0
wk112904 0
wk120604 1

< SNIP >

 

[Guest]

I'm sorry, I'm not really familiar with the IIF function. What would it look
like here?
Thanks much.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Perhaps this (Access 2002):

PARAMETERS [Start Date?] Date, [End Date?] Date;
SELECT "WK" & week_begin - (WeekDay(week_begin)-2) As WeekOf, Count(*)
AS Widgets
FROM table_name
WHERE Created BETWEEN [Start Date?] And [End Date?]
GROUP BY "WK" & week_begin - (WeekDay(week_begin)-2)

The calculation converts the date in WEEK_BEGIN into the Monday date in
which the WEEK_BEGIN date resides. But, it pushes Sundays into the
following Monday. IOW, Sunday is considered part of the week beginning
on the following Monday. If no work is done on Sunday then everything
is fine; otherwise, I'll leave the fix to that little problem to you.
Hint: IIF() function.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQi39fIechKqOuFEgEQJ4aACfSdDs6IFmTasshB6W/TMhD7KahKUAoNpL
EVAlaU+4+S9hbT8UYvTFFjV+
=XMna
-----END PGP SIGNATURE-----

Actually, this isn't what I had in mind.

I would like my output to be summarized as follows. (Note that the name of
the week is "WKmmddyy", where mmddyy is always a Monday.)

WEEK_BEGIN Num_Widgets_Created
----------------- -----------------------------
wk110104 2
wk110804 0
wk111504 0
wk112204 0
wk112904 0
wk120604 1

< SNIP >

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

This:

"WK" & week_begin - (WeekDay(week_begin) - IIf(WeekDay(week_begin)=1,
5,2)) As WeekOf,

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQi9cAYechKqOuFEgEQLymACfTNkqSGVyJbTFZmgWXnLECyTWtjAAoLKm
OoUNB8BbDq+xeaxeE1cqz69R
=5FIw
-----END PGP SIGNATURE-----

I'm sorry, I'm not really familiar with the IIF function. What would it look
like here?
Thanks much.



:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Perhaps this (Access 2002):

PARAMETERS [Start Date?] Date, [End Date?] Date;
SELECT "WK" & week_begin - (WeekDay(week_begin)-2) As WeekOf, Count(*)
AS Widgets
FROM table_name
WHERE Created BETWEEN [Start Date?] And [End Date?]
GROUP BY "WK" & week_begin - (WeekDay(week_begin)-2)

The calculation converts the date in WEEK_BEGIN into the Monday date in
which the WEEK_BEGIN date resides. But, it pushes Sundays into the
following Monday. IOW, Sunday is considered part of the week beginning
on the following Monday. If no work is done on Sunday then everything
is fine; otherwise, I'll leave the fix to that little problem to you.
Hint: IIF() function.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQi39fIechKqOuFEgEQJ4aACfSdDs6IFmTasshB6W/TMhD7KahKUAoNpL
EVAlaU+4+S9hbT8UYvTFFjV+
=XMna
-----END PGP SIGNATURE-----

 

[Guest]

Thanks again! Much obliged.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

This:

"WK" & week_begin - (WeekDay(week_begin) - IIf(WeekDay(week_begin)=1,
5,2)) As WeekOf,

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQi9cAYechKqOuFEgEQLymACfTNkqSGVyJbTFZmgWXnLECyTWtjAAoLKm
OoUNB8BbDq+xeaxeE1cqz69R
=5FIw
-----END PGP SIGNATURE-----

I'm sorry, I'm not really familiar with the IIF function. What would it look
like here?
Thanks much.



:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Perhaps this (Access 2002):

PARAMETERS [Start Date?] Date, [End Date?] Date;
SELECT "WK" & week_begin - (WeekDay(week_begin)-2) As WeekOf, Count(*)
AS Widgets
FROM table_name
WHERE Created BETWEEN [Start Date?] And [End Date?]
GROUP BY "WK" & week_begin - (WeekDay(week_begin)-2)

The calculation converts the date in WEEK_BEGIN into the Monday date in
which the WEEK_BEGIN date resides. But, it pushes Sundays into the
following Monday. IOW, Sunday is considered part of the week beginning
on the following Monday. If no work is done on Sunday then everything
is fine; otherwise, I'll leave the fix to that little problem to you.
Hint: IIF() function.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQi39fIechKqOuFEgEQJ4aACfSdDs6IFmTasshB6W/TMhD7KahKUAoNpL
EVAlaU+4+S9hbT8UYvTFFjV+
=XMna
-----END PGP SIGNATURE-----