breaking a month into weeks

T

Tara

I need some help with a query that would break a month
into weeks. What I am dealing with are "activities" that
must occur within specific weeks in order for a client to
be "billable" that month. For example a level I client
must have 3 visits all occuring in different weeks OR 2
visits and 2 attempted visits, again in different weeks.
Any idea how I can make the month break into weeks (wk 1,
wk 2, etc.)? It seems like I have seem this somewhere
before but I can't find the example now!

Thanks in advance,
Tara
 
T

Tom Ellison

Dear Tara:

Since a "month" is not exactly composed of "weeks" there will need to
be some additional definition here. For example, if every "week"
starts on Sunday and belongs to the month in which that Sunday falls,
that is a more specific definition. But, it is certainly not the only
possible definition.

Naturally then, every month would contain either 4 or 5 weeks, right?

I would start out with a numerical methodology of identifying every
week. This would be in 3 parts: the year, the month (1-12) and the
week (1-5). I would then write a function that returns this value in
a YYMMWW format. That would provide a value on which to start.

If you query this for a client with the above week value, and do so
DISTINCTly you will then have a list of weeks for any month (a month
is the YYMM portion). By COUNTing them in both visits and attempted
visits, you could then filter (with a HAVING clause) to show which
clients would be billed according to the rules you suggest.

I'm not sure how much additional detail you may need. For an expert,
this could be just a couple of hours work, possibly a bit less. If
this will be a giant learning experience for you then it may take a
couple of days study and work.

We can break it down into pieces and attack them one at a time, now
that we know what those pieces are (assuming you agree on the approach
to be used).

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
T

Tara

Thanks for the start Tom! I will definately need some
time to work with it as I am by no means an expert. I'll
be away from work for two weeks, but will try your
suggestions when I get back and hopefully I can get
somewhere with it.

Thanks again
Tara
 
Top