Newbie question regarding SQL Statement for chart

[jj2222]

Hi all,

I'm trying to get a weekly bar chart to display correctly in MS Access 2007. Tried and tried changing the SQL but no luck, new to Access and SQL. Any help would be much appreciated!

I have a weekly bar chart on a form which is based on a query and need it to display data grouped by week with Monday being the first day of the week and Sunday being the last day. The chart currently displays every Sunday separately on the chart. In essence, a six day week grouped as one bar and Sunday being another bar on the chart for every Sunday. I need each bar in the chart to represent a 7 day week including Sunday. Code below doesn't do that.




HERE IS THE CURRENT CODE:

SELECT (Format([SaleDate],"WW  'YY")),Sum([SaleSubTotal]) AS [SumOfSaleSubTotal] FROM [qry_ProductPopularityAndSales]   GROUP BY (Year([SaleDate])*CLng(54) + DatePart("ww",[SaleDate],0)-1),(Format ([SaleDate],"WW  'YY"));

Thanks for any help on this

[June7]

If you want Monday as first day of week:

DatePart("ww",SaleDate,vbMonday)
or
Format(Date(),"ww",vbMonday)

vbMonday numeric value is 2

DatePart("ww",SaleDate,2)
or
Format(Date(),"ww",2)

[jj2222]

Hi and thanks for you reply!

I did read about the number 2 being monday prior to posting on this forum and did try to change the 0 to 2 but the main issue remains unresolved even after changing the "0" to "2". The main issue is that sunday is displayed as a seperate bar on it's own. for every week, the Sunday amount is displayed as a separate bar and the other 6 remaining days amounts as another bar. There are 2 bars for every week instead of 1.

Still unresolved.

Thanks for your suggestion,

JJ

[June7]

I deal with graphing issues best when I can work directly with the db. If you want to provide, follow instructions at bottom of my post.

[jj2222]

The issue appears to be with "+ DatePart("ww",[SaleDate],0)-1" section of the statement...If I remove this part, the chart appears as it should "1 bar for every week" but the week starts on Sunday and ends on Saturday.

THIS STATEMENT WORKS BUT START AND END OF WEEK NOT CORRECT

SELECT (Format([SaleDate],"WW  'YY")),Sum([SaleSubTotal]) AS [SumOfSaleSubTotal] FROM 
[qry_ProductPopularityAndSales] GROUP BY Year([SaleDate])*CLng(54),(Format
([SaleDate],"WW  'YY"));

Any help in making Monday the first day of the week and Sunday the last without adding this "DatePart" into the SQL statement would be appreciated.

Thanks

[June7]

I showed two examples of expressions that set Monday as first day of week. If you want to include year part:

Format([SaleDate], "ww 'yy", 2)

[jj2222]

Problem solved Thank You!
I added 2 in both sections and removed the"DatePart" section (DatePart produces 2 bars for every week which is not correct)

Final code that works and produces correct weekly chart with 1 bar per week and a Monday-Sunday week

SELECT (Format([SaleDate],"WW  'YY",2)),Sum([SaleSubTotal]) AS [SumOfSaleSubTotal] FROM [qry_ProductPopularityAndSales] GROUP BY Year([SaleDate])*CLng(54),(Format ([SaleDate],"WW  'YY",2));

Thanks you for your help, much appreciated!