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:
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 