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

    What is the format to get a graph to sum week? (2002)

    I have a graph which gets its data from a qryGrossIncome. Some days there is 0 GI others have GI. I want to get a graph with week ending totals for the year. 52 weeks. I want the Week to end on a Friday, so I want to have the query sum the days from Saturday to Friday and use that sum for the data point. I don't what criteria I should enter in the query grid. Any help is gratefully appreciated.
    Thanks
    Frank

  2. #2
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: What is the format to get a graph to sum week? (2002)

    Take a look at the DatePart function. You can create a new column in your query based on DatePart(), and specify the correct starting DayOfWeek to account for your Friday requirement. Then sum the values based on the week number, and base your graph on the new query.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  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: What is the format to get a graph to sum week? (2002)

    Thanks,
    Would it be imposing on you to give me the format for this to put in the query. I have two fields now: Date and Amount.

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: What is the format to get a graph to sum week? (2002)

    Here is the SQL for a Table1 with fields called DateOfPurchase and Amount

    SELECT Sum(amount) AS SumOfAmount, DatePart("ww",[dateofpurchase]) AS WeekNo
    FROM Table1
    GROUP BY DatePart("ww",[dateofpurchase]);

    Open a new query and view it as SQL. Paste the above code into the query (change field names and table name as appropriate).
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Mishawaka, Indiana, USA
    Posts
    145
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: What is the format to get a graph to sum week? (2002)

    Shouldn't the code DatePart("ww",[dateofpurchase]) be written as follows to stipulate that Saturday be the start of the week?

    DatePart("ww", 7, [dateofpurchase])

    See DatePart Function in Access help. You may also may want to include a firstweekofyear argument.

  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: What is the format to get a graph to sum week? (2002)

    Actually, SELECT Sum(amount) AS SumOfAmount, DatePart("ww",[dateofpurchase]) AS WeekNo
    FROM Table1
    GROUP BY DatePart("ww",[dateofpurchase]); does work BUT you are right, How do you set and control the beginning date for the week.
    I want to end the week at Thursday at 2:00 PM. Could you give the code?

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

    Re: What is the format to get a graph to sum week? (2002)

    As Tom wrote, look up the DatePart function in the online help or the Object Browser. It has two additional optional arguments:
    DatePart(interval, date[,firstdayofweek[, firstweekofyear]])

    If you want the week to start on Saturday, use
    DatePart("ww", [dateofpurchase], 7)
    in both the SELECT and the GROUP BY part of the SQL statement.
    (I think Tom got the arguments in the wrong order)

    You mention that you want the week to start on Saturday and end on Thursday at 2:00 PM. I don't understand this. What happens if dateofpurchase is between Thursday 2 PM and Saturday (midnight)?

  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: What is the format to get a graph to sum week? (2002)

    Got it working last night thanks for both of your help.
    Sorry about the confusion. The week ends at Thursday at 2 PM in the afternoon and obviously starts at 2:01 Thursday.
    Thanks,
    Frank

Posting Permissions

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