Month Report with Weeks

  • Thread starter Thread starter channell
  • Start date Start date
C

channell

Hello,

I feel that I generally have a good idea of how reports work in access.
However, I am stumped and I need some help please.

I have a month report, showing totals for employees performance. Now, I
need to have the dates broken up into weeks. So in essence, if I have 5
employees on this report when I run it currently, it shows their totals for
the month. What I need is for this report to break down (by week) how each
employee did.

It would need to look something (not exactly) like this:

Schumacher, Bob
Week: 2nd-9th Performance: 5
Week: 10th-17th Performance: 3
Week: 18th-25th Performance: 4

Smith, Bill
Week: 2nd-9th Performance: 4

............ And so forth.

What would it take for my report to look like this? Thank you so very much
for your help! It is much appreciated!

-Scott Channell
 
What does your data look like? Post sample data with table and field names
with datatype.
 
Thank you Here are the relevant tables

tEmployees
EmployeeID (PK)
fName
lName
....and so forth

tDailyinfo
DailyinfoID (PK)
EmployeeID(FK)
WorkDate (Date)
Performance (Number)
and other information...

See, I can make a report(with parameters) that will show me my employees
according to what dates I pick (IE: 02/01/2009 to 02/28/2009) However, I was
curious to find out if and how I could break up the month to show weeks, as I
mention in the Original Post. I hope this is the information you were
looking for. Thank you!

-Scott Channell
 
Ok. So how do you define weeks in a month?
How do you handle partial weeks in a month?

Jan 2009
1-3 first Week
4-10 2nd week
11-17 3rd week
18-24 4th week
25-31 5th week

April 2009
1-4 1st week or Mar29 to April 4 is 1st week
5-11 2nd week
12-18 3rd week
19-25 4th week
26-30 5th week or Apr 26 to May 2 is 1st week of May

The simplest method here would probably be to build a Calendar table of
specific dates and two columns to set Week Number (1-5) and Week Month
Number (1-12) for each date. Then you could use that table to identify
the week number and month number for each date based on your rules.

Then you can decide if April 1 2009 is in week 5 of March(3) or Week 1
of April (4).

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
To be honest, I really don't need partial weeks in a month. How about this:

A Sunday thru Saterday breakdown. So, if i input the 1st thru the 18th of
this month, I would need it to return the following:

1st thru 7th: Performance 3
8th thru 14th: Performance 4
15th thru 18th: Performance 4

Thanks
 
Try this --
SELECT [lName] & ", " & [fName] AS Employee, ((Format([WorkDate],"d")\7)+1)
& " thru " & ((Format([WorkDate],"d")\7)+1)*7 AS Week, tDailyinfo.Performance
FROM tEmployees LEFT JOIN tDailyinfo ON tEmployees.EmployeeID =
tDailyinfo.EmployeeID
WHERE (((Format([WorkDate],"yyyym"))=[Enter year - 2006] & [Enter month -
3]));
 
Back
Top