Week number - How do you reset?

[BillG]

I have a table with 5 years data. I have a date field in the table. I pull a query with data for 2011 and 2012 by date.


I can calculate the week number from a formula for both 2011 and 2012.
But how can I reset the week number for 2011 to equal the week number for 2012?

Week(Prior Year) = Week(Current Year)????

Week(Date) + 1 does not work because of the difference in week number for a day between the two years.

[JoeM]

Note quite sure I understand.

What is your formula you are currently using for Week number?
Can you post a small sample of your data, and what you are trying to do with it?
If we see it, it may become clearer what you are trying to do, and what challenges you are facing.

[BillG]

Table1
Date	Store No	Sales	Year	Month	Week
8/5/2012	238	1000	2012	8	32
8/5/2011	238	500	2011	8	31
Week =	DatePart("ww",Table1.date])
I am running a report Grouped by "Week" then by "Day"
I am comparing sales for day 5 for 2011 and 2012
But day 5 for 2011 shows up in week 31 while day 5 for 2012 shows up in week 32
I need to reset the week number for 2011 from 31 to equal the week number for 2012 of 32
in order for day 5 to show one line in week 32 for 2011 and 2012
My report looks like this:
Day	store No	2011	2012
5	238	500
Week 31 Total		500	0
5	238		1000
Week 32 Total		0	1000
I need it to look like this:
Day	store No	2011	2012
5	238	500	1000
Week 32 Total		500	1000

[JoeM]

Where/how is this "day" calculation happening?

[BillG]

in the query.
Day: DatePart("d",[Table1]![Date])

[JoeM]

That returns the day of the month, not the weekday.

If you are trying to break it down by Week Number, then Day of the Week, take a look at using the Weekday function instead of the DatePart function.
See here: http://www.techonthenet.com/access/f...te/weekday.php

[BillG]

You asked me where I got Day from.

I am not looking for weekday.

I am looking at week which is in the table. for 2011 I need to reset week 31 to week 32 in my report to make the days align.

[JoeM]

I am having difficulty understanding your logic (the "what and the "why" of what you are trying to do).

Are you looking for something like this?

Week: DatePart("ww",[Table1]![Date]) + IIf(Year([Table1]![Date])=2011,1,0)

[ssanfu]

Attachment 9131
Look at this image. You can see that the week number changes depending on the year.

It sounds like you want the day to always fall in the same "week number".
I would suggest a lookup table. Two columns: "DayNum" and "WeekNum".

1 , 1
2 , 1
3 , 1
4 , 1
5 , 1
6 , 1
7 , 2
8 , 2
etc

Then do a look up for the day to get the week number.
So day 5 (in any month) will always be week number 1 and day 13 will always be week number 2.

[BillG]

Your Imaage is correct. Day 5 is a different week number every month and every year depending on what weekday the 1st of the month falls on.

But your lookup is incorrect because you are showing day 5 is always week 1 and that is not true.
See your image above for 1/5/2011 and 1/5/2012 are different week numbers.

In your image I need a formula in my query to reset week number for 1/5/2011 from a 1 to a 2.
So when I rin my report by week then week 1 will include day 5 for both years.

If I don't reset prior year week numer to equal current year week number then day 5 shows up in week 1 for 1/52012 but day 5 shows up again in week 2 for 1/5/2011.

This is why I need a formula in my query to reset prior year week number to current year week number because it can change each month between years the results.

It seems like a simple Algebraic expression in Access but I can't figure out what it is.

Crosstab Query
Day	Week Prior Year	Week Current Year	Week Reset	2011 Sales	2012 Sales
5	31		32	$ 1,200
5		32	32		$ 2,000
what is a formula in Week Reset column to make week for both years and same day to 32?

[ssanfu]

But your lookup is incorrect because you are showing day 5 is always week 1 and that is not true.

Does it really matter which week the day actually falls in if YOU set the week number for every month/year? So in any month, day 1 to 7 is ALWAYS week 1. Doesn't matter what week number it really is, for you it is always week 1. No calculation needed - just a look up.

Week Num      Days
1            1 - 7
2            8 - 14
3           15 - 21
4           22 - 28
5           29 - 31
or
4           22 - 31

(actual table might look like my previous example)

The point is, using a look up table, You decide which week the day falls in. It sounds like you are using the week number to group on; it doesn't really matter which week it really is.
Or am I still not understanding what you want??

[BillG]

No. The week is not alway week1. The calendar decides when it is.
In my example, 8/5/2011 is week1, but 8/5/2012 is week2.

I have solved my calculation problem for this thread.

I appreciate your help. Thanks.