PC Review


Reply
Thread Tools Rate Thread

week by week information

 
 
Marc S
Guest
Posts: n/a
 
      17th Sep 2004
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.
 
Reply With Quote
 
 
 
 
Duane Hookom
Guest
Posts: n/a
 
      18th Sep 2004
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?

--
Duane Hookom
MS Access MVP


"Marc S" <(E-Mail Removed)> wrote in message
news:352b01c49d01$5c8f9ba0$(E-Mail Removed)...
> 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.



 
Reply With Quote
 
 
 
 
Marc S
Guest
Posts: n/a
 
      20th Sep 2004
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"

>-----Original Message-----
>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?
>
>--
>Duane Hookom
>MS Access MVP
>
>
>"Marc S" <(E-Mail Removed)> wrote in

message
>news:352b01c49d01$5c8f9ba0$(E-Mail Removed)...
>> 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.

>
>
>.
>

 
Reply With Quote
 
Duane Hookom
Guest
Posts: n/a
 
      20th Sep 2004
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

--
Duane Hookom
MS Access MVP
--

"Marc S" <(E-Mail Removed)> wrote in message
news:2e7901c49f12$cbb7e1f0$(E-Mail Removed)...
> 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"
>
> >-----Original Message-----
> >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?
> >
> >--
> >Duane Hookom
> >MS Access MVP
> >
> >
> >"Marc S" <(E-Mail Removed)> wrote in

> message
> >news:352b01c49d01$5c8f9ba0$(E-Mail Removed)...
> >> 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.

> >
> >
> >.
> >



 
Reply With Quote
 
Gary Walter
Guest
Posts: n/a
 
      20th Sep 2004
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

"Marc S" wrote:
> 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"
>
> >-----Original Message-----
> >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?
> >
> >--
> >Duane Hookom
> >MS Access MVP
> >
> >
> >"Marc S" <(E-Mail Removed)> wrote in

> message
> >news:352b01c49d01$5c8f9ba0$(E-Mail Removed)...
> >> 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.

> >
> >
> >.
> >



 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I show day-of-week in 'Calendar' -> "Work Week" & "Week" SkwrHdz Microsoft Outlook Discussion 1 8th May 2009 03:50 PM
Work week or week view advancing to next week =?Utf-8?B?ZnMwOQ==?= Microsoft Outlook Calendar 7 13th Oct 2006 09:29 PM
Week by week break down in Inbox Nicolas Verhaeghe Microsoft Outlook 4 11th Apr 2005 01:16 AM
How can the current week be the default starting week in Month View? Howie Microsoft Outlook 0 9th Mar 2005 07:33 PM
a set of date fields that update from week to week. =?Utf-8?B?Y21wdHJiaWw=?= Microsoft Word Document Management 3 12th Oct 2004 07:57 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 06:22 AM.