Regret this is not an easy problem to describe...
I want to create a graph that shows two lines, Year 1 and
Year 2 volumes summed by week from a table that has date
and volume fields. I can do this but my solution is
awfully messy and involves creating separate queries for
Year 1 and Year 2 and in the Year 1 query creating another
field, link_date, that adds 365 days to the date field so
that in a third query I can join the Year 1 link_date to
the Year 2 date.
There is a further complication that if the data is
aggregated by week, and the start of Year 1 is not 1
January, when the graph is created the weeks are sorted in
ascending order which is logical but wrong for my
purpose. For example say the first week in the series is
week 36, then a full twelve month period will span the
year end and should increment from 36 to 52 and then
continue from week 1 to week 35 of the following year.
But Access sorts the week in ascending order instead of
actual date order.
Another related issue involves using the Datepart "ww"
function to aggregate by week, this can create a week 53
with less than 7 days data and then the following week, 1,
has the balance of days for the week. But it varies from
year to year, so when the data is compared by week, say on
a like for like basis, there is a mismatch i.e. a week
with 3 days is compared with a week with 4 days.
I have a solution for this which says that if
datepart "ww" returns 53 then make the week value = 1 so
the week 53 data gets reallocated to the following year's
week 1.
I've struggled to make the problem clear but hope it's
understandable.
Thanks for any help.
Mike Collard
I want to create a graph that shows two lines, Year 1 and
Year 2 volumes summed by week from a table that has date
and volume fields. I can do this but my solution is
awfully messy and involves creating separate queries for
Year 1 and Year 2 and in the Year 1 query creating another
field, link_date, that adds 365 days to the date field so
that in a third query I can join the Year 1 link_date to
the Year 2 date.
There is a further complication that if the data is
aggregated by week, and the start of Year 1 is not 1
January, when the graph is created the weeks are sorted in
ascending order which is logical but wrong for my
purpose. For example say the first week in the series is
week 36, then a full twelve month period will span the
year end and should increment from 36 to 52 and then
continue from week 1 to week 35 of the following year.
But Access sorts the week in ascending order instead of
actual date order.
Another related issue involves using the Datepart "ww"
function to aggregate by week, this can create a week 53
with less than 7 days data and then the following week, 1,
has the balance of days for the week. But it varies from
year to year, so when the data is compared by week, say on
a like for like basis, there is a mismatch i.e. a week
with 3 days is compared with a week with 4 days.
I have a solution for this which says that if
datepart "ww" returns 53 then make the week value = 1 so
the week 53 data gets reallocated to the following year's
week 1.
I've struggled to make the problem clear but hope it's
understandable.
Thanks for any help.
Mike Collard
