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

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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).

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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)?

Reply | Quote

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

Reply | Quote