I require a query to count repairs by week

[trevorC via AccessMonster.com]

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.

 

[trevorC via 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

 

[Marshall Barton]

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])

 

[trevorC via AccessMonster.com]

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

 

[Bob Barrows]

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