Results 1 to 9 of 9

Query: group by specific dates

  1. #1
    irish634 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2012
    Posts
    42

    Query: group by specific dates

    I need some ideas on how to do this.


    Our scrap week runs from Wednesday to the following Tuesday. (ex. 9/4 to 9/10)

    I am querying a table based on this going back 12 weeks. (ex. 6/19 to 9/10). I can do this fine.

    But, if I try to group by week, access of course groups by Sun-Sat and my totals are off....

    Does anyone have any idea how I could manually force the week group to our criteria?

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,124
    Please show us your table(s) design, your query SQL and some sample records.

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,906
    This is a fairly common topic.
    How do you count weeks - weeks of month or weeks of year? How do you want to handle weeks that cross months or years? Do you always want a full 7-day period regardless of month or year? If yes to the last then calculate the first day of your week (Wednesday) for each record and use that for grouping value. Weekday() function has argument to specify first day of week (as do some other date manipulation functions). Consider:

    FDW: datefield-Weekday(datefield,4)+1
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    irish634 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2012
    Posts
    42
    Here is a sample of how I handle the date and how I am currently grouping.

    Sample.zip

    June, thank you for the input... I will play with it

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,124
    Using June's advice[thanks June], I mocked up a table and query to group by weekstarting date (wednesdays).
    The sample deals with scrap pickup in lbs. with various pickupdates and scrap amounts.
    Here is the query sql --I hope it's helpful.

    Code:
    SELECT [pickupdate]-Weekday([Pickupdate],4)+1 AS WeekStartDate_WeekNO
    , Sum(tblScrapMockup.[ScrapWeight_lbs]) AS SumOfScrapWeight_lbs
    FROM tblScrapMockup
    WHERE pickupdate >= Date() -(7*13)
    GROUP BY [pickupdate]-Weekday([Pickupdate],4)+1
    ORDER BY [pickupdate]-Weekday([Pickupdate],4)+1;

  6. #6
    irish634 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2012
    Posts
    42
    Thank you both, I have some manipulating to do, both still sum the wrong totals.

    I got it... thank you. They both work correctly...
    (FYI there is an error in the spreadsheet example (sum of 9/10)

    (I want to group by the ending date)
    With June's: I used ([dtm_Date]-Weekday([dtm_Date],4)+7)

    With orange's:
    Code:
    SELECT [dtm_Date]-Weekday([dtm_Date],4)+7 AS WeekStartDate_WeekNO
    , Sum([lng_Quantity]) AS SumOfQuantity, Sum([dbl_TotalCost]) As SumOfTotalCost
    FROM tbl_Sample
    WHERE (((dtm_Date) Between DateAdd("d",-83,EndingTuesday(#9/13/2019#)) And EndingTuesday(#9/13/2019#)))
    GROUP BY [dtm_Date]-Weekday([dtm_Date],4)+7
    ORDER BY [dtm_Date]-Weekday([dtm_Date],4)+7;

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,124
    Try

    Code:
    SELECT [DTM_Date]-Weekday([DTM_Date],4)+1 AS WeekStartDate_WeekNO
    , Sum(lng_Quantity) AS SumOfQuantity
    , Sum(dbl_TotalCost) AS SumOfCost
    FROM tbl_Sample
    WHERE DTM_Date >= Date() -(7*13)
    GROUP BY [DTM_Date]-Weekday([DTM_Date],4)+1
    ORDER BY [DTM_Date]-Weekday([DTM_Date],4)+1;

  8. #8
    irish634 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2012
    Posts
    42
    Quote Originally Posted by orange View Post
    Try...
    See above, I just had to manipulate a little.
    Thank you both very much again.

  9. #9
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,124

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 08-15-2019, 09:39 AM
  2. Replies: 1
    Last Post: 03-23-2015, 09:42 AM
  3. Replies: 1
    Last Post: 11-22-2013, 12:30 PM
  4. Replies: 9
    Last Post: 10-21-2013, 03:31 PM
  5. Dates by Group?
    By coliver in forum Reports
    Replies: 2
    Last Post: 11-07-2009, 09:03 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums