Group Result by weeks

[Grooz13]

Hello I have a little problem. I have order for different day and I would like to create a query that would look like this

Date_Ordered(a day field in my tables but I would like it to be grouped by week) and a sold price where it sums for a certain week so my query would have 2 column

Date Ordered(with the year and grouped by week)
Sold_Sums (sums of the sold_price for the grouped week)



and I don't really know how to do this I tried in a report but it gave me all the money we made for grouped week but it didn't wrote the week so I had a bunch of prices with no date... so
anyone ?

[ajetrumpet]

I'm not quite making out your explanation, but consider a table like this:

orderdate	SoldPrice
9/10/2007	$1,595.00
3/12/2009	$3,005.00
2/2/2009	$869.71
7/29/2008	$9,401.40
4/30/2007	$803.00
7/15/2008	$1,766.60
5/4/2008	$615.00
9/7/2008	$151.17
4/5/2009	$542.00
3/30/2007	$2,020.00
3/20/2007	$2,020.00
1/28/2007	$453.51
1/23/2009	$6,267.60
4/28/2009	$119.96
3/19/2008	$677.50
4/10/2009	$5,223.00
11/29/2007	$151.17
12/12/2007	$4,178.40
8/30/2008	$320.00
6/6/2008	$4,001.95
1/27/2007	$160.00
7/30/2007	$453.51
5/30/2008	$9,015.00
9/9/2007	$19,847.40

to sort this table by the week and see the amount you sold for each week, you can write this:

SELECT "Year " & year([orderdate]) & ", " & "Week " & 
FORMAT([orderdate], "ww") AS TimePeriod, sum([SoldPrice]) AS WeekSales

FROM table

GROUP BY "Year " & year([orderdate]) & ", " & "Week " & 
FORMAT([orderdate], "ww")

the output is something like:

TimePeriod	WeekSales
Year 2007, Week 12	$2,020.00
Year 2007, Week 13	$2,020.00
Year 2007, Week 18	$803.00
Year 2007, Week 31	$453.51
Year 2007, Week 37	$21,442.40
Year 2007, Week 4	$160.00
Year 2007, Week 48	$151.17
Year 2007, Week 5	$453.51
Year 2007, Week 50	$4,178.40
Year 2008, Week 12	$677.50
Year 2008, Week 19	$615.00
Year 2008, Week 22	$9,015.00
Year 2008, Week 23	$4,001.95
Year 2008, Week 29	$1,766.60
Year 2008, Week 31	$9,401.40
Year 2008, Week 35	$320.00
Year 2008, Week 37	$151.17
Year 2009, Week 11	$3,005.00
Year 2009, Week 15	$5,765.00
Year 2009, Week 18	$119.96
Year 2009, Week 4	$6,267.60
Year 2009, Week 6	$869.71

Regardless of how you manipulate a date field, Access can still read and interpret it if it is grouped correctly. In this example, Access is adding the SoldPrice field together base on Column 1's output. It really isn't grouping by time actual dates or time periods, but you can make it look that way.

As far as sorting goes, I haven't figured it out through queries so I always sort the data based on date in the source table before querying it.

[Grooz13]

thank you very much, one more tool in my access knowledge