week by week information

Discussion in 'Microsoft Access Queries' started by Marc S, Sep 17, 2004.

  1. Marc S

    Marc S Guest

    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.
     
    Marc S, Sep 17, 2004
    #1
    1. Advertisements

  2. Marc S

    Duane Hookom Guest

    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" <> wrote in message
    news:352b01c49d01$5c8f9ba0$...
    > 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, Sep 18, 2004
    #2
    1. Advertisements

  3. Marc S

    Marc S Guest

    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" <> wrote in

    message
    >news:352b01c49d01$5c8f9ba0$...
    >> 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.

    >
    >
    >.
    >
     
    Marc S, Sep 20, 2004
    #3
  4. Marc S

    Duane Hookom Guest

    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" <> wrote in message
    news:2e7901c49f12$cbb7e1f0$...
    > 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" <> wrote in

    > message
    > >news:352b01c49d01$5c8f9ba0$...
    > >> 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, Sep 20, 2004
    #4
  5. Marc S

    Gary Walter Guest

    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" <> wrote in

    > message
    > >news:352b01c49d01$5c8f9ba0$...
    > >> 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.

    > >
    > >
    > >.
    > >
     
    Gary Walter, Sep 20, 2004
    #5
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Guest
    Replies:
    5
    Views:
    268
    Gregory Paret
    Nov 9, 2004
  2. Guest

    Simple Week over Week Query

    Guest, Jun 27, 2005, in forum: Microsoft Access Queries
    Replies:
    5
    Views:
    286
    Jeff Boyce
    Jul 14, 2005
  3. Guest
    Replies:
    2
    Views:
    652
    Guest
    Apr 11, 2006
  4. Guest

    convert week of 06-35 into "Week of 8/27/2006"

    Guest, Sep 5, 2006, in forum: Microsoft Access Queries
    Replies:
    7
    Views:
    169
    Guest
    Sep 6, 2006
  5. Igorin

    Query "From last week's Friday to this week's Thursday"

    Igorin, Sep 30, 2008, in forum: Microsoft Access Queries
    Replies:
    2
    Views:
    348
    Igorin
    Sep 30, 2008
Loading...

Share This Page