Month Report with Weeks

[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

 

[karl dewey]

What does your data look like? Post sample data with table and field names
with datatype.

 

[channell]

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

 

[John Spencer]

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
'====================================================

 

[channell]

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

 

[KARL DEWEY]

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]));