Prev: 2 independent criteria
Next: Syntax error when using names with apostrophe
From: trevorC via AccessMonster.com on 3 May 2010 21:20
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

From: trevorC via AccessMonster.com on 3 May 2010 22:11
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

From: Marshall Barton on 3 May 2010 22:31
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]
From: trevorC via AccessMonster.com on 3 May 2010 22:48
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/Forums.aspx/access-queries/201005/1

From: Bob Barrows on 4 May 2010 03:29
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"


 | 
Pages: 1
Prev: 2 independent criteria
Next: Syntax error when using names with apostrophe