Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I need to first, determine where a new week begins then total the number of days for each week. Please see attached.

    Can someone please offer insight for this.

    Any suggestions are appreciated.
    Attached Files Attached Files
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Why do most weeks end on Friday, but one of them on Thursday (July 9)?

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='787497' date='04-Aug-2009 13:42']Why do most weeks end on Friday, but one of them on Thursday (July 9)?[/quote]

    In the attached example that was an error on my part. The week actually ended on 7/10/2009. I've made the change and have attached a new example. With that said, if a holiday is in the middle of the week, it has to be accounted for as well. i.e. if 7/4/2009 was on a Friday, the company would be off work, therefore there would be 3 days between activity.
    Attached Files Attached Files
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    By the way, do you want to do this in Access or in Excel? You have posted in the Access forum, but the attachment is an Excel workbook.

  5. #5
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='787633' date='05-Aug-2009 13:22']By the way, do you want to do this in Access or in Excel? You have posted in the Access forum, but the attachment is an Excel workbook.[/quote]

    I want to do this in Access (in a query preferrably. Even if the function is in a module, I can call the function within a query). I only put the example in Excel.

    Thanks.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Create a query based on the table.
    Add a calculated column to the query grid:

    d: [dDateOfAttempt]-Weekday([dDateOfAttempt],2)

    Also add the Count field.
    Select View | Totals.
    Change the Total option for the Count field to Sum.
    Switch to datasheet view.

    The SQL for this query is

    SELECT [dDateOfAttempt]-Weekday([dDateOfAttempt],2) AS d, Sum(NameOfTable.Count) AS SumOfCount
    FROM NameOfTable
    GROUP BY [dDateOfAttempt]-Weekday([dDateOfAttempt],2);

    where NameOfTable is the name of the table.

  7. #7
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='787652' date='05-Aug-2009 14:11']Create a query based on the table.
    Add a calculated column to the query grid:

    d: [dDateOfAttempt]-Weekday([dDateOfAttempt],2)

    Also add the Count field.
    Select View | Totals.
    Change the Total option for the Count field to Sum.
    Switch to datasheet view.

    The SQL for this query is

    SELECT [dDateOfAttempt]-Weekday([dDateOfAttempt],2) AS d, Sum(NameOfTable.Count) AS SumOfCount
    FROM NameOfTable
    GROUP BY [dDateOfAttempt]-Weekday([dDateOfAttempt],2);

    where NameOfTable is the name of the table.[/quote]
    Sorry for slow response, thanks Hans, works perfectly
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •