Same Week, Different Years....

[prosfora]

I have the following expression which works nicely to group all of my dates
into their respective weeks of the year, by week dates beginning Mondays:

WeekStart: ([MyDateField]+1)-DatePart("w",[MyDateField],2)

However, since the week beginning 12/26/05 crosses over both '05 and '06 (as
week 53 and week 1), I get two groups of dates recorded for the week of
12/26/05 when I run my query.

Since my chart is based on weekly totals, I can't have two separate totals
with the same 'week beginning' date.

How can I adjust my code to get one total group of dates for the week of
12/26/05?

Help greatly-needed and much appreciated!....

 

[Gary Walter]

I have the following expression which works nicely to group all of my dates
into their respective weeks of the year, by week dates beginning Mondays:

WeekStart: ([MyDateField]+1)-DatePart("w",[MyDateField],2)

However, since the week beginning 12/26/05 crosses over both '05 and '06
(as
week 53 and week 1), I get two groups of dates recorded for the week of
12/26/05 when I run my query.

Since my chart is based on weekly totals, I can't have two separate totals
with the same 'week beginning' date.

How can I adjust my code to get one total group of dates for the week of
12/26/05?

Hi prosfora,

Do you wish to get "starting Monday" for
every [MyDateField]?

WeekStart: [MyDateField]
- WeekDay([MyDateField]+2

good luck,

gary

 

[prosfora via AccessMonster.com]

I have the following expression which works nicely to group all of my dates
into their respective weeks of the year, by week dates beginning Mondays:

[quoted text clipped - 11 lines]

How can I adjust my code to get one total group of dates for the week of
12/26/05?

Hi prosfora,

Do you wish to get "starting Monday" for
every [MyDateField]?

WeekStart: [MyDateField]
- WeekDay([MyDateField]+2

good luck,

gary

Thanks Gary,

I actually already have the starting Monday dates, but I'm getting two
returns for week 12/26/2005. Here's a more detailed explanation, along with
my code:

Here's the SQL for the query (TotalIn), along with the query (GroupIn)that it
is based on.

My intention with GroupIn is to assign to each individual weekday, the week
date (beginning Monday) in which that weekday occurs (and group them by weeks)
.. This works.

Then, TotalIn is supposed to total each of those week groups; and that total
represents the total number of weekdays that occurred during each week (ie -
7/11/2005 has 5 total; 7/18/2005 has 8 total, etc).

This works too; however, I get 12/26/2005 with a total of 6, and another
12/26/2005 with a total of 3 because that week is both week 53 and week 1 of
the next year.

I want only one total number (9 in this case) for week 12/26/2005 so that I
will only have only one value for that week on my chart.

Here are the SQL's:

(1st is the GroupIn query where Datein equals each weekday from my table
'Weeks')

SELECT [Weeks].Datein, [Weeks].WeekIN, ([Datein]+1)-DatePart("w",[Datein],2)
AS WeekStart
FROM [Weeks];

(2nd is the TotalIn query)

SELECT [GroupIn].WeekIN AS Week, Count(*) AS [TotalIN], [GroupIn].WeekStart
FROM [GroupIn]
GROUP BY [GroupIn].WeekIN, [GroupIn].WeekStart;

Hope this helps, (thanks)---

 

[prosfora via AccessMonster.com]

[quoted text clipped - 13 lines]

gary

Thanks Gary,

I actually already have the starting Monday dates, but I'm getting two
returns for week 12/26/2005. Here's a more detailed explanation, along with
my code:

Here's the SQL for the query (TotalIn), along with the query (GroupIn)that it
is based on.

My intention with GroupIn is to assign to each individual weekday, the week
date (beginning Monday) in which that weekday occurs (and group them by weeks)
. This works.

Then, TotalIn is supposed to total each of those week groups; and that total
represents the total number of weekdays that occurred during each week (ie -
7/11/2005 has 5 total; 7/18/2005 has 8 total, etc).

This works too; however, I get 12/26/2005 with a total of 6, and another
12/26/2005 with a total of 3 because that week is both week 53 and week 1 of
the next year.

I want only one total number (9 in this case) for week 12/26/2005 so that I
will only have only one value for that week on my chart.

Here are the SQL's:

(1st is the GroupIn query where Datein equals each weekday from my table
'Weeks')

SELECT [Weeks].Datein, [Weeks].WeekIN, ([Datein]+1)-DatePart("w",[Datein],2)
AS WeekStart
FROM [Weeks];

(2nd is the TotalIn query)

SELECT [GroupIn].WeekIN AS Week, Count(*) AS [TotalIN], [GroupIn].WeekStart
FROM [GroupIn]
GROUP BY [GroupIn].WeekIN, [GroupIn].WeekStart;

Hope this helps, (thanks)---

Solved with answer in another posting---

I simply added a '3' argument to the following portion of my code:

DatePart("w",[Datein],2)

so it now reads:

DatePart("w",[Datein],2,3)

Which eliminates week '53' and combines the totals for week 12/26/05 as week
'52'