Problem with a query

[Art Vandaley]

Hi All,

I'm about to get insane!

I have a query (SQL output is at bottom) which I try grouping the results
according to week numbers. My data starts with 52nd week of 2006. when I
input data for 52nd week of 2007 my query adds it to value of 52nd week of
2006. I have same trouble with 1st week of 2008. My query adds 2008's 1st
week's value into 1st week of 2007 and only shows 1st week of 2007 but I
need to see weeks of 2007, 2008, and so on.

I have below result:

NAME---MINofDATE---WEEK---SUMofHOUR1---SUMofHOUR2

SOMEB.-2006------------52-------20-----------------10
SOMEB.-2007------------1--------20-----------------10
SOMEB.-2007------------2--------10-----------------5
SOMEB.-2007------------3--------10-----------------5
..
..
..
SOMEB.-2007------------51-------10-----------------5

But I need below result:

NAME---MINofDATE---WEEK---SUMofHOUR1---SUMofHOUR2

SOMEB.-2006------------52-------10-----------------5
SOMEB.-2007------------1--------10-----------------5
SOMEB.-2007------------2--------10-----------------5
SOMEB.-2007------------3--------10-----------------5
..
..
..
SOMEB.-2007------------51-------10-----------------5
SOMEB.-2007------------52-------10-----------------5
SOMEB.-2008------------1--------10-----------------5


Does anybody have an idea?

SELECT DISTINCTROW TABLE.NAME, Min(TABLE.DATE) AS MinOfDATE, TABLE.WEEK,
Sum(TABLE.[HOUR1]) AS [Sum Of HOUR1], Sum(TABLE.[HOUR2]) AS [Sum Of HOUR2]
FROM TABLE
GROUP BY TABLE.NAME, TABLE.WEEK
ORDER BY Min(TABLE.DATE), TABLE.WEEK;

 

[George Nicholson]

You need to add a field to your query that combines Year and Week. Then
Group and Order on that.

SELECT....
Year(TABLE.DATE) & Format(Table.Week,"00") As YearWeek
......
GROUP BY TABLE.NAME, Year(TABLE.DATE) & Format(Table.Week,"00")
ORDER BY Year(TABLE.DATE) & Format(Table.Week,"00");