Results 1 to 13 of 13
Thread: Weekly Sums (2002)

20020605, 03:39 #1
 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

20020605, 06:56 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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

20020606, 02:26 #3
 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

20020606, 05:22 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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.

20020606, 08:31 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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

20020607, 04:25 #6
 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

20020607, 06:06 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Weekly Sums (2002)
<P ID="edit" class=small>Edited by HansV on 07Jun02 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

20020607, 06:47 #8
 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

20020607, 07:32 #9
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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

20020607, 18:37 #10
 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

20020610, 05:35 #11
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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

20020610, 10:15 #12
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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.)

20020612, 06:40 #13
 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>