Results 1 to 13 of 13
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    San Francisco, CA, USA
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Weekly Sums (2002)

    Still having trouble making a query for the record source for a graph. (Weekly Estimates)
    I have two tables: tblCustomers and tblJobs, which are linked 1 to many. I have a field in tblJobs: Estimate1 and DateJob. I have data for 1999, 2000,2001 and 2002. I want to start the the first week of 1999. The week ends at Thursday at 2:00 PM and starts on Friday. I'm trying to get the DatePart Function to start the first week of 1999 and sum the weeks for these years week by week through 2002 on one graph. I get week 53 at the bottom of the query and can't get sums for the week. I need the week date part running along the Y axis on the graph (bottom) and the weekly ending sum of Estimate1 for the X axis data points.
    Need help on the SQL statement which if you can post would really help.
    Thanks,
    Frank Hoeffer

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Weekly Sums (2002)

    Hello Frank,

    How about something like

    SELECT Year([DateJob])*100+DatePart("ww",[DateJob],6) AS Week, Sum(Estimate1) AS WeekSum
    FROM tblJobs
    GROUP BY Year([DateJob])*100+DatePart("ww",[DateJob],6);

    This will group by years and weeks (starting on Friday, courtesy of the 6 as the third argument in DatePart) in the form yyyyww, e.g. 199901 and 200215.

    You can display the year/week combination differently if you wish:

    SELECT Year([DateJob]) & "W" & Format(Format([DateJob],"ww",6),"00") AS Week, Sum(Estimate1) AS WeekSum
    FROM tblJobs
    GROUP BY Year([DateJob]) & "W" & Format(Format([DateJob],"ww",6),"00");

    This will show 1999W01 and 2002W15.

    HTH, Hans

  3. #3
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    San Francisco, CA, USA
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Weekly Sums (2002)

    Thanks HansV,
    Well it works great BUT...two things I want to have the 200215 show as W/E Apr 6,.2002 for each week ending summary. I put in JobDate in the query but it that takes away the summary and lists all of the values.
    ALSO, what does Year do, is it a function and and why multiply by 100?
    Frank

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Weekly Sums (2002)

    I'll have to think a bit about the first question.

    To answer the second question: Year, Month and Day are functions that take a date and return (surprise, surprise) the year, month and day as numeric values.

    For instance, if JobDate is April 6, 2002, then Month(JobDate) will return 4.

    One way to get the query to group by year and week number, is by combining them into one numeric value.
    Say, the year is 2002 and the week number is 8. If you multiply year by 100, you get 200200. Now adding 8 yields 200208.
    And if the year is 1999 and the week number is 45, you get 199900 + 45 = 199945.
    This numeric value ensures that the dates will be grouped and sorted the way you want: first by year, and within year by week number.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Weekly Sums (2002)

    Edited to correct wrong field name and to add explanation

    About your first question: try the following. You may have to fiddle with it a bit to get it to work exactly the way you want.

    SELECT [DateJob]-([DateJob] Mod 7) AS Week, Sum(Estimate1) AS WeekSum
    FROM tblJobs
    GROUP BY [DateJob]-([DateJob] Mod 7);

    In the design view of the query, activate the Properties window. Select the Week field and enter as Format:
    W/E mmm d, yyyy

    Explanation:

    Access stores dates as numbers - the number of days since December 30, 1899.

    [DateJob] Mod 7 is the remainder when you divide the numeric value of [DateJob] by 7.
    7, of course, is the number of days in a week (for Jan Karel Pieterse: yes, I knew that!).

    For Saturdays, the remainder is 0, for Sundays, it is 1, for Mondays it is 2, etc.

    If you subtract this remainder from the date, you get the last Saturday on or before the date.

    HTH,
    Hans

  6. #6
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    San Francisco, CA, USA
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Weekly Sums (2002)

    Hi HansV,
    It's almost there. A couple of issues. I put some dummy data for for June 1 to June 9. I want the WE Date to end of a Thursday. I tried different numbers for mod but can't get it to end the week on the 6th of June.
    Also could you give me the code for the formating I can't seem to get the WE to be added to the date.
    Thanks
    Frank

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Weekly Sums (2002)

    <P ID="edit" class=small>Edited by HansV on 07-Jun-02 09:06.</P>Hello Frank,

    Sorry for submitting botched versions of this post earlier.

    I think this should do it:

    SELECT [DateJob]+6-(([DateJob]+1) Mod 7) AS Week, Sum(Estimate1) AS WeekSum
    FROM tblJobs
    GROUP BY [DateJob]+6-(([DateJob]+1) Mod 7);

    The format property of the calculated Week field should be

    <pre>"W/E "mmm d yyyy</pre>


    Regards,
    Hans

  8. #8
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    San Francisco, CA, USA
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Weekly Sums (2002)

    Hi HansV
    Looking good! How do you do the Format Function to it could you please give me the code.
    Thanks
    Frank

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Weekly Sums (2002)

    Hello Frank,

    I have attached a demo Access 97 database.
    Unzip it, convert it and open qryTest in design view.
    Click in the Week field and look at the Format property in the Properties window.

    Regards,
    Hans
    Attached Files Attached Files

  10. #10
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    San Francisco, CA, USA
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Weekly Sums (2002)

    Hi HansV,
    Well it's getting there. And thanks for your gracious help in sending a demo.
    I did not know about the format property dialog when you right click on the Week field.
    It adds the W/E in the datasheet but it does not add it on the Y axis as W/E.
    Also when would you use the format function in the expression on the sql grid.
    Frank Hoeffer

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Weekly Sums (2002)

    Hello Frank,

    You can use the Format function in the definition of the calculated field itself. But in that case, the field becomes a text field. That means that it will be sorted as text, so June comes before May. (Also, you wouldn't be able to use it for further calculations.)

    To get around this, you can display the formatted field while grouping (and thus sorting) on the unformatted field:

    SELECT Format([DateJob]+6-(([DateJob]+1) Mod 7),"""W/E ""mmm d"", ""yyyy") AS Week, Sum(tblJobs.Estimate1) AS WeekSum FROM tblJobs GROUP BY [DateJob]+6-(([DateJob]+1) Mod 7);

    Regards, Hans

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Weekly Sums (2002)

    As an addition, here is a new demo, including the modified query and a form with a chart.
    (As in my previous attachment, it is a zipped Access 97 database.)
    Attached Files Attached Files

  13. #13
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    San Francisco, CA, USA
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Weekly Sums (2002)

    Hi HansV,
    Thank you very much for your help.
    You went way above the call of duty including the demo and your explanation.
    My client has been trying to solve this problem for a very long time and when he saw your work a grin about 12 inches wide appeared on his face.
    Thank you very much.
    Frank Hoeffer <img src=/S/groovin.gif border=0 alt=groovin width=21 height=21>

Posting Permissions

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