Problem with a query

A

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;
 
G

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");
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help 1
Access 2007 "group by" creating problem 4
Dealing with a Pivot Table (when I don't want to) 0
crosstab query? 12
IF then SUM 5
Cumulative Query 3
How i make the query 3
Min/Max Dates 1

Top