Crosstab Query Query

[Kevin Labore]

HI

I am attempting to make a crosstab query to have:
the last 12 weeks in sale in cols
the depts in rows
I made a month and some other crosstab queries without a problem

The problem I think is that for example if we are in week 3 for this year I
need the other 9 weeks or such of the previous year
I did try using the criteria with between but that didnt work maybe because
I am using datepart to get the week for the col I tried the below 2 but that
didnt give the results I needed

Between DatePart("ww",Now()) And DateAdd("ww",-13,Now())
Between DateAdd("ww",-1,Now()) And DateAdd("ww",-1,13,Now())

this is the col info for the cross tab query

Weeks: DatePart("ww",[deptdate])

Maybe I should go with with a weekending date?

can someone tell me how to address this?


thanks for any assistance


Kevin

 

[Michel Walsh]

Hi,


There is a problem with the way Office counts the weeks, near a new
year. With most of the settings, that leaves incomplete weeks (except on
some particular cases), so stats are not reliable since a given week may not
have all its seven days.


As example, week 1 of 2005 is made of a single day:


? DatePart("ww", #12-31-2004#)
53
? DatePart("ww", #1-1-2005#)
1
? DatePart("ww", #1-2-2005#)
2


since the second of January is part of week 2.


It is definitively preferable to use explicit interval:


FromDate, UpToDate, WeekNumber
....
#12-19-2004# #12-26-2004# 200452
#12-26-2004# #1-2-2005# 200501
#1-2-2005# #1-9-2005# 200502
....

Note that the UpToDAte is exclusive (use <, not <= , neither BETWEEN) :


.... ON theDateTimeStamp >= Intervals.FromDate AND theDate <
Intervals.UpToDate



Hoping it may help,
Vanderghast, Access MVP

 

[Kevin Labore]

HI

thanks for answering my 2nd question that I was wondering of how to deal
with with the week problem

Hi,


There is a problem with the way Office counts the weeks, near a new
year. With most of the settings, that leaves incomplete weeks (except on
some particular cases), so stats are not reliable since a given week may not
have all its seven days.


As example, week 1 of 2005 is made of a single day:


? DatePart("ww", #12-31-2004#)
53
? DatePart("ww", #1-1-2005#)
1
? DatePart("ww", #1-2-2005#)
2


since the second of January is part of week 2.


It is definitively preferable to use explicit interval:


FromDate, UpToDate, WeekNumber
...
#12-19-2004# #12-26-2004# 200452
#12-26-2004# #1-2-2005# 200501
#1-2-2005# #1-9-2005# 200502
...

Note that the UpToDAte is exclusive (use <, not <= , neither BETWEEN) :


... ON theDateTimeStamp >= Intervals.FromDate AND theDate <
Intervals.UpToDate



Hoping it may help,
Vanderghast, Access MVP

HI

I am attempting to make a crosstab query to have:
the last 12 weeks in sale in cols
the depts in rows
I made a month and some other crosstab queries without a problem

The problem I think is that for example if we are in week 3 for this year
I
need the other 9 weeks or such of the previous year
I did try using the criteria with between but that didnt work maybe
because
I am using datepart to get the week for the col I tried the below 2 but
that
didnt give the results I needed

Between DatePart("ww",Now()) And DateAdd("ww",-13,Now())
Between DateAdd("ww",-1,Now()) And DateAdd("ww",-1,13,Now())

this is the col info for the cross tab query

Weeks: DatePart("ww",[deptdate])

Maybe I should go with with a weekending date?

can someone tell me how to address this?


thanks for any assistance


Kevin

 

[Duane Hookom]

I would set the column heading expression to
ColHead: "Wk" & DateDiff("ww",[deptdate],Date())
Don't set any criteria and set the column headings property to
Wk0,Wk1,Wk2,Wk3,...Wk11

--
Duane Hookom
MS Access MVP
--

HI

thanks for answering my 2nd question that I was wondering of how to deal
with with the week problem

Hi,


There is a problem with the way Office counts the weeks, near a new
year. With most of the settings, that leaves incomplete weeks (except on
some particular cases), so stats are not reliable since a given week may not
have all its seven days.


As example, week 1 of 2005 is made of a single day:


? DatePart("ww", #12-31-2004#)
53
? DatePart("ww", #1-1-2005#)
1
? DatePart("ww", #1-2-2005#)
2


since the second of January is part of week 2.


It is definitively preferable to use explicit interval:


FromDate, UpToDate, WeekNumber
...
#12-19-2004# #12-26-2004# 200452
#12-26-2004# #1-2-2005# 200501
#1-2-2005# #1-9-2005# 200502
...

Note that the UpToDAte is exclusive (use <, not <= , neither BETWEEN) :


... ON theDateTimeStamp >= Intervals.FromDate AND theDate <
Intervals.UpToDate



Hoping it may help,
Vanderghast, Access MVP

HI

I am attempting to make a crosstab query to have:
the last 12 weeks in sale in cols
the depts in rows
I made a month and some other crosstab queries without a problem

The problem I think is that for example if we are in week 3 for this year
I
need the other 9 weeks or such of the previous year
I did try using the criteria with between but that didnt work maybe
because
I am using datepart to get the week for the col I tried the below 2 but
that
didnt give the results I needed

Between DatePart("ww",Now()) And DateAdd("ww",-13,Now())
Between DateAdd("ww",-1,Now()) And DateAdd("ww",-1,13,Now())

this is the col info for the cross tab query

Weeks: DatePart("ww",[deptdate])

Maybe I should go with with a weekending date?

can someone tell me how to address this?


thanks for any assistance


Kevin