Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2006
    Posts
    17

    When Week Numbers Collide!

    I have the following expression which works nicely to group all of my dates into their respective weeks of the year, by week dates beginning Mondays:

    WeekStart: ([MyDateField]+1)-DatePart("w",[MyDateField],2)

    However, since the week beginning 12/26/05 crosses over both '05 and '06 (as week 53 and week 1), I get two groups of dates recorded for the week of 12/26/05 when I run my query.

    Since my chart is based on weekly totals, I can't have two separate totals with the same 'week beginning' date.



    How can I adjust my code to get one total group of dates for the week of 12/26/05?

    Help greatly-needed and appreciated!....

  2. #2
    StepUP is offline Advanced Beginner
    Windows 7 64bit Access 2000
    Join Date
    Dec 2005
    Posts
    99
    Why are you saying that you get 2 groups of dates? For any day of the week of 12/26/05 you should get only one date in the query in the "WeekStart" column.

    Perhaps I'm not undestanding what your query is trying to do. Please post the SQL for the query.

  3. #3
    Join Date
    Mar 2006
    Posts
    17
    Here's the SQL for the query (TotalIn), along with the query (GroupIn)that it is based on.

    My intention with GroupIn is to assign to each individual weekday, the week date (beginning Monday) in which that weekday occurs (and group them by weeks). This works.

    Then, TotalIn is supposed to total each of those week groups; and that total represents the total number of weekdays that occurred during each week (ie - 7/11/2005 has 5 total; 7/18/2005 has 8 total, etc).

    This works too; however, I get 12/26/2005 with a total of 6, and another 12/26/2005 with a total of 3 because that week is both week 53 and week 1 of the next year.

    I want only one total number (9 in this case) for week 12/26/2005 so that I will only have only one value for that week on my chart.

    Here are the SQL's:

    (1st is the GroupIn query where Datein equals each weekday from my table 'Weeks')

    SELECT [Weeks].Datein, [Weeks].WeekIN, ([Datein]+1)-DatePart("w",[Datein],2) AS WeekStart
    FROM [Weeks];

    (2nd is the TotalIn query)

    SELECT [GroupIn].WeekIN AS Week, Count(*) AS [TotalIN], [GroupIn].WeekStart
    FROM [GroupIn]
    GROUP BY [GroupIn].WeekIN, [GroupIn].WeekStart;

    Hope this helps, (thanks)---

  4. #4
    Join Date
    Mar 2006
    Posts
    17
    Solved with answer in another posting---

    I simply added a '3' argument to the following portion of my code:

    DatePart("w",[Datein],2)

    so it now reads:

    DatePart("w",[Datein],2,3)

    Which eliminates week '53' and combines the totals for week 12/26/05 as week '52'

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

Similar Threads

  1. Insert Dash between set of Numbers
    By Jhankish in forum Programming
    Replies: 2
    Last Post: 04-19-2007, 08:02 AM
  2. Retrieving Week of Year in a date range
    By Korvega in forum Queries
    Replies: 2
    Last Post: 05-27-2006, 06:29 AM
  3. Week number wrong
    By Nikki17 in forum Queries
    Replies: 6
    Last Post: 03-18-2006, 10:01 PM
  4. Day of Week Totals
    By ddog171 in forum Queries
    Replies: 1
    Last Post: 03-18-2006, 07:01 PM
  5. query problem, oppointments for one week
    By keithsrobinson in forum Queries
    Replies: 2
    Last Post: 02-18-2006, 02:28 PM

Posting Permissions

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