A Weekly Count

  • Thread starter Thread starter NotGood@All
  • Start date Start date
N

NotGood@All

I'm trying to get a count of things that happended for the past 6 weeks. I'm
trying to modify this code:

SELECT (DateCreated) AS Expr1, Count(*) AS [Output]
FROM qryForStatsPageOnly
WHERE (qryForStatsPageOnly.DateCreated)<=DateAdd("ww",6,Date())
GROUP BY (DateCreated)
ORDER BY (DateCreated) DESC;

but no matter what I put in place of the "6" it works just like I have "d"
in the "ww". When I read help on DateAdd, it reads like all you have to do
is replace a "d" with "m" with "yyyy".....
 
I'm trying to get a count of things that happended for the past 6 weeks. I'm
trying to modify this code:

SELECT (DateCreated) AS Expr1, Count(*) AS [Output]
FROM qryForStatsPageOnly
WHERE (qryForStatsPageOnly.DateCreated)<=DateAdd("ww",6,Date())
GROUP BY (DateCreated)
ORDER BY (DateCreated) DESC;

but no matter what I put in place of the "6" it works just like I have "d"
in the "ww". When I read help on DateAdd, it reads like all you have to do
is replace a "d" with "m" with "yyyy".....
Click to expand...

Your query as written is doing two things wrong: first, it's asking for all
records where DateCreated is earlier than six weeks IN THE FUTURE (you're
adding +6 weeks to Date() and finding all DateCreated less than that value).
Another possible problem if I'm interpreting your subject correctly is that
you're grouping by DateCreated so rather than a count per week you're getting
a count per day (or even per record if DateCreated contains a time portion).

Try

SELECT DateCreated, Count(*) AS Output
FROM qryForStatsPageOnly
WHERE DateCreated >= DateAdd("ww", -6, Date())

to get a count per day; post back if you want a count by week, indicating how
you want the result to appear.
 
If you want what's been modified in the past six weeks, I'd think you'd want

WHERE (qryForStatsPageOnly.DateCreated)>=DateAdd("ww",-6,Date())

Alternatively, you could use

WHERE (qryForStatsPageOnly.DateCreated)>=DateAdd("d",-42,Date())
 
NotGood@All said:
I'm trying to get a count of things that happended for the past 6 weeks. I'm
trying to modify this code:

SELECT (DateCreated) AS Expr1, Count(*) AS [Output]
FROM qryForStatsPageOnly
WHERE (qryForStatsPageOnly.DateCreated)<=DateAdd("ww",6,Date())
GROUP BY (DateCreated)
ORDER BY (DateCreated) DESC;

but no matter what I put in place of the "6" it works just like I have "d"
in the "ww". When I read help on DateAdd, it reads like all you have to do
is replace a "d" with "m" with "yyyy".....
Click to expand...


If you want the past 6 weeks, maybe it should be more like:

SELECT DateDiff("d", [DateCreated], Date()) \ 7 As Expr1,
Count(*) AS [Output]
FROM qryForStatsPageOnly
WHERE DateDiff("d", [DateCreated], Date()) \ 7 < 6
GROUP BY DateDiff("d", [DateCreated], Date()) \ 7
 
Back
Top