week by week information

[Marc S]

How would I go about generating information on a week by
week basis? I have a table that has numerous entries from
a labor database. Each employee submits data that records
time in 15 minute increments by Job Number (EmpNo, JobNo,
WorkHours, etc). I want to genterate a query / report
that shows a summary of information for all employees on a
rolling week basis by Job Number. I want the query /
report to show:

WeekEnding Job1 Job2 Total

9/3/2004 800 400 1200
9/10/2004 750 350 1100
9/17/2004 810 400 1210

And so on. I can get a single week but the on-going
information has got me stumped.

Thanks.

 

[Duane Hookom]

Do you have more field names to share? I don't see any field that suggests a
date. Can you also provide some sample data that would result in your sample
output?

 

[Marc S]

Duane,

Thanks for the interest.

The data is in a large table that records information from
an electronic timesheet. The "column" headings include
EmployeeNumber, JobNumber, Date, StartTime, StopTime,
BillHours as well as some other data; however, these
records should provide what is needed for this query. I
have produced numerous valuable reports that focus on a
single date range (e.g. last week, last month, etc.) but I
would like a report that shows summary information week
over week for the past 26 weeks (our weeks end on Friday).
For selected job numbers, I would like to see the total
BillHours week over week (I think I've seen this referred
to as Rolling Weeks). The report might look something like
this where the numbers under the Job Number are a sum of
the BillHours:

Week Ending Job 123.11 Job 234.11 Job 123.12
9/17/04 750.25 500.75 275.00
9/10/04 685.25 587.50 325.25
9/3/04 550.50 675.00 250.50

Like I said, I have a lot of experience with a single date
range, it's the multiple date range that I can't seem to
figure out. I've played with the DateSerial function, but
can't get that down to the week level, only the month.

Thanks again for the help and let me know if you need
addtional information or clarification.

Marc

Regarding sample data in the table, it looks something like
this (we have ~1200 records per week):

EmpNo JobNo Date StartTime StopTime BillHours
102 123.11 9/14/04 800 1145 3.75
105 123.11 9/17/04 1300 1500 2.00
201 234.11 9/16/04 900 1200 3.00
201 123.12 9/14/04 1300 1700 4.00
102 467.11 9/14/04 800 1145 3.75
105 467.11 9/17/04 1300 1500 2.00
402 234.11 9/16/04 900 1200 3.00
402 467.12 9/14/04 1300 1700 4.00

I will not need all job numbers for the report. For
example, I may want the sum of BillHous only for job
numbers that end in "2"

 

[Duane Hookom]

You should be able to create a crosstab report that has a row heading of
something like:
WeekOf: DateAdd("d",-Weekday([Date])+6,[Date])
Set the Column Heading to the JobNumber. You may have issues with this since
your job numbers contain periods. For information on reporting crosstabs,
check out the samples at http://www.invisibleinc.com/divFiles.cfm?divDivID=4

 

[Gary Walter]

Hi Marc,

PMFBI

Just in case you might not know how to set
the criteria to get 26 week span for Duane's

WeekOf: DateAdd("d",-Weekday([Date])+6,[Date])

First, you might want to define what "past 26 weeks"
means. Today is Sep 20, 2004.

Do you always want to include the current week?

EndingFriday (this week) = Date()-(Weekday(Date())-6)
which gives 9/24/2004

or do you *not* want to include the current week?

EndingFriday (last week) = Date()-(Weekday(Date())+1)
which gives 9/17/2004

Once this is determined, then we can compute the
"beginning Friday" of your 26-week span.

Assuming "EndingFriday" to be Friday of current week,

BeginFriday = Date() - (Weekday(Date())-6) - 25*7
which gives 4/2/2004
(you could simplify this formula...I left as is so you
could see what we did better)

So, in Criteria row under "WeekOf"
(all on one line)

BETWEEN (Date() - (Weekday(Date())-6) - 25*7)
AND (Date()-(Weekday(Date())-6))

Apologies again for butting in.

Good luck,

Gary Walter