I require a query to count repairs by week

Hi,
I have a query as shown, that gives me the number of repairs per week, but
the output shows an entry for each day of the week with the total as well,
can i just get the total per week and the week number or week start date even.


requested o/p =

sample only
Week start 1/1/10 74
Week start 7/1/10 13
Week start 15/1/10 116


SELECT DatePart("ww",[out]) AS [Repairs per Week], gamrep.Out
FROM gamrep;

Any help would be great.

--
Message posted via http://www.accessmonster.com

I so far have this now...

SELECT Count(gamrep.[In]) AS CountOfDate, Format(DateAdd("d",-DatePart("w",
gamrep![in],1)+2,gamrep![in]),"dddd") & " " & DateAdd("d",-DatePart("w",
gamrep![in],1)+2,gamrep![in]) AS WeekStarting
FROM gamrep
GROUP BY Format(DateAdd("d",-DatePart("w",gamrep![in],1)+2,gamrep![in]),
"dddd") & " " & DateAdd("d",-DatePart("w",gamrep![in],1)+2,gamrep![in])

This gives me the data i do need, but shows it sorted by number not date
EG
230 Monday 1/01/2007 - Jan
237 Monday 1/02/2010 - Feb
003 Monday 1/03/2004 - Mar
112 Monday 1/04/2010 - Apr
...
032 Monday 12/01/2009 - Jan
and so on
can this be sorted by date
EG
first week of january
second week of january
third week of january
fourth week of january
first week in Febuary
and so on.

Is it possible to sort this query this way.
regards
TrevorC

--
Message posted via http://www.accessmonster.com

trevorC via AccessMonster.com wrote:
SELECT Count(gamrep.[In]) AS CountOfDate, Format(DateAdd("d",-DatePart("w",
gamrep![in],1)+2,gamrep![in]),"dddd") & " " & DateAdd("d",-DatePart("w",
gamrep![in],1)+2,gamrep![in]) AS WeekStarting
FROM gamrep
GROUP BY Format(DateAdd("d",-DatePart("w",gamrep![in],1)+2,gamrep![in]),
"dddd") & " " & DateAdd("d",-DatePart("w",gamrep![in],1)+2,gamrep![in])

This gives me the data i do need, but shows it sorted by number not date
EG
230 Monday 1/01/2007 - Jan
237 Monday 1/02/2010 - Feb
003 Monday 1/03/2004 - Mar
112 Monday 1/04/2010 - Apr
..
032 Monday 12/01/2009 - Jan
and so on
can this be sorted by date
EG
first week of january
second week of january
third week of january
fourth week of january
first week in Febuary
and so on.



Just add:
ORDER BY DateAdd("d", -DatePart("w", gamrep![in], 1)+2,
gamrep![in])

BTW, there's nothing wrong with using DatePart, but it's a
little easier to type and to read if you use the WeekDay
function:

DateAdd("d",-WeekDay(gamrep![in])+2,gamrep![in])

--
Marsh
MVP [MS Access]

Thanks for the update on this, but I am getting the error shown below

*****************
You tried to execute a query that does not include the specified expression
'DateAdd("d", -DatePart("w", gamrep![in], 1)+2,gamrep![in])' as part of an
aggregate function.
*****************

code from SQL with orderby added

SELECT Count(gamrep.[In]) AS CountOfDate, Format(DateAdd("d",-DatePart("w",
gamrep![in],1)+2,gamrep![in]),"dddd") & " " & DateAdd("d",-DatePart("w",
gamrep![in],1)+2,gamrep![in]) AS WeekStarting
FROM gamrep
GROUP BY Format(DateAdd("d",-DatePart("w",gamrep![in],1)+2,gamrep![in]),
"dddd") & " " & DateAdd("d",-DatePart("w",gamrep![in],1)+2,gamrep![in])
ORDER BY DateAdd("d", -DatePart("w", gamrep![in], 1)+2,gamrep![in]);

regards
TrevorC

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/201005/1

trevorC via AccessMonster.com wrote:
Thanks for the update on this, but I am getting the error shown below

*****************
You tried to execute a query that does not include the specified
expression 'DateAdd("d", -DatePart("w", gamrep![in],
1)+2,gamrep![in])' as part of an aggregate function.
*****************

code from SQL with orderby added

SELECT Count(gamrep.[In]) AS CountOfDate,
Format(DateAdd("d",-DatePart("w",
gamrep![in],1)+2,gamrep![in]),"dddd") & " " &
DateAdd("d",-DatePart("w", gamrep![in],1)+2,gamrep![in]) AS
WeekStarting
FROM gamrep
GROUP BY
Format(DateAdd("d",-DatePart("w",gamrep![in],1)+2,gamrep![in]),
"dddd") & " " &
DateAdd("d",-DatePart("w",gamrep![in],1)+2,gamrep![in]) ORDER BY
DateAdd("d", -DatePart("w", gamrep![in], 1)+2,gamrep![in]);


Four things:
1. You don't have to group by the formatted week start date. In fact, the
query will perform a little better if you minimize the number of
calculations done on the field being grouped by.
2. You don't have to include the field being grouped by in the SELECT
clause.
3. You can use the grouped-by field in an expression in the SELECT clause
without including that new expression in the GROUP BY clause.
4. You can order by a field in the GROUP BY clause that is not listed in the
SELECT clause.

The result of all those points:
SELECT
Format(DateAdd("d",-DatePart("w",gamrep![in],1)+2,gamrep![in]),"dddd") & " "
& DateAdd("d",-DatePart("w",gamrep![in],1)+2,gamrep![in]) AS WeekStarting,
Count(*) AS CountOfDate
FROM gamrep
GROUP BY DateAdd("d",-DatePart("w",[gamrep]![in],1)+2,[gamrep]![in])
ORDER BY DateAdd("d",-DatePart("w",[gamrep]![in],1)+2,[gamrep]![in]);

--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"