Running Count By Week

[TW Bake]

Hi All,

I am trying to produce a running count of product sales by week. Not
all products are sold each week. So I need a query to increment each
week then count the number of products that preceded it. For Example:

Wk Prod A Prod B
2/1 10 11
2/8 12 11
2/15 13 15

The challenge is that I don't want to have to put all possible dates
in a table, the scope could be several years. So I'd like the week
field to be auto-generated from a designated starting point and go to
a designated end point (about 2 years out). The Product counts should
be cumulative. The final query will be used to produce a cumulative
line chart.

I've tried looking up incremental counts but haven't found anything
that works. Any help is appreciated.

 

[John W. Vinson]

Hi All,

I am trying to produce a running count of product sales by week. Not
all products are sold each week. So I need a query to increment each
week then count the number of products that preceded it. For Example:

Wk Prod A Prod B
2/1 10 11
2/8 12 11
2/15 13 15

The challenge is that I don't want to have to put all possible dates
in a table, the scope could be several years. So I'd like the week
field to be auto-generated from a designated starting point and go to
a designated end point (about 2 years out). The Product counts should
be cumulative. The final query will be used to produce a cumulative
line chart.

I've tried looking up incremental counts but haven't found anything
that works. Any help is appreciated.

One thing to consider: a 36525 row table is a pretty small table, and contains
a hundred years of dates. "The scope could be several years"? So what?? Disk
is cheap!

That said, it's very common to include a little utility table - I call mine
Num, with one Long Integer field N, values 0 through 10000 or so. Tiny!

A query

SELECT DateAdd("d", 7*N, Date()) FROM Num WHERE N <= 730;

will get you every seventh day for the next two years. Tweak the starting
point to a Sunday, include the table in your query, and you should be on your
way.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[David Kaye]

I am trying to produce a running count of product sales by week.

At the risk of pissing off people who don't like intrinsic Jet functions,
there is a function that will determine the week of the year. For instance in
the database I'm working on right now, the following returns the week of the
year and the date of songs entered into my database:

select datepart("ww",filedate), filedate from songs

The "ww" refers to a 1 or 2-digit week number, so the string "4" is returned
for the date filedate "1/21/2010 1:37:05 AM" in the table I'm using to
demonstrate this, or "34" for the date "8/17/2009 5:36:37 PM"

So, you could do a group by clause to select records by week of year.

 

[John W. Vinson]

At the risk of pissing off people who don't like intrinsic Jet functions,
there is a function that will determine the week of the year. For instance in
the database I'm working on right now, the following returns the week of the
year and the date of songs entered into my database:

select datepart("ww",filedate), filedate from songs

The "ww" refers to a 1 or 2-digit week number, so the string "4" is returned
for the date filedate "1/21/2010 1:37:05 AM" in the table I'm using to
demonstrate this, or "34" for the date "8/17/2009 5:36:37 PM"

So, you could do a group by clause to select records by week of year.

You will need to use care for the first and last week of the year; since a
year doesn't consist of an even number of weeks, it's possible (depending on
the options chosen in DatePart) for a given date to be both in the first week
of one year AND the last week of the previous.

It's not a bar to doing this; you just need to understand how the function
works. See the VBA help, it gives examples.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[TW Bake]

Thanks John and David for your help. Keeping a counter table makes
good sense. Much, much simpler than creating the dates on the fly
through a query. Works like a charm of course.

I'll try the Group By with the datepart function next.

Thanks again!

TW

 

[David W. Fenton]

(e-mail address removed) (David Kaye) wrote in

At the risk of pissing off people who don't like intrinsic Jet
functions,

No one has ever objected to using DatePart() or any of the date
manipulation functions, except when using them makes a query unable
to use indexes.

You might want to see a doctor about having that chip on your
shoulder removed.

 

[David Kaye]

You might want to see a doctor about having that chip on your
shoulder removed.

Actually, it was a calcium deposit, which could be removed via ultrasound.
Things are fine now. Thanks for your concern.