Access SQL - date sieve (possible duplicate post)

[G Lykos]

Greetings! Could use a little orientation.

Given a table Events:

Event SomeDate
A 4/1/07
A 4/10/07
B 3/26/07

Want to count occurrences of the events per week ending on Sundays.

So for above, in the Sunday date range 4/1 - 4/15, result would be:

Sunday A B
4/1/07 1 1
4/8/07 0 0
4/15/07 1 0

Created a table Weeks:
Sunday
4/1/07
4/8/07
4/15/07

A first SQL query goes:
SELECT Sunday, COUNT (*)
FROM Weeks LEFT JOIN [Events] on (SomeDate) BETWEEN (Sunday-6) AND (Sunday)
GROUP BY Sunday

Output is:
4/1/07 2
4/8/07 1
4/15/07 1

The 4/1 and 4/15 counts are true. Question 1: I'm picking up a null (I
think) on 4/8, which should be 0 rather than 1, but don't know how to figure
out from where or what to do about it.

If I insert a WHERE:
SELECT Sunday, COUNT (*)
FROM Weeks LEFT JOIN [Events] on (SomeDate) BETWEEN (Sunday-6) AND (Sunday)
WHERE (Event LIKE "A" OR Event LIKE "B")
GROUP BY Sunday

then I lose the empty week 4/8, with results:
4/1 2
4/15 1

Also, I tried:
SELECT Sunday, COUNT (Event LIKE "A"), COUNT (Event LIKE "B")
FROM Weeks LEFT JOIN [Weeks] on (SomeDate) BETWEEN (Sunday-6) AND (Sunday)
GROUP BY Sunday

hoping to get to:
Sunday A B
4/1/07 1 1
4/8/07 0 0
4/15/07 1 0

Question 2: While the syntax doesn't cause an error, the results of the two
COUNT expressions are not affected in the least by their arguments, both of
which yield the same counts as COUNT (*), so COUNT is apparently not
intended to be used in this fashion.

Would appreciate any guidance to the two questions, as well as suggested
course of action.

Thanks,
George

 

[Michel Walsh]

COUNT(*) will not return 0, since that would mean there is no row in the
group, also as in no row to display that 0. (Crosstab query is an exception
since its rows are not generated in exactly the same context).

COUNT(fieldName) could return 0, though, if it happens that no initial row
has a not null value under the fieldName column, for the given group.

Anyhow, in your case, it seems you could try a crosstab:

TRANSFORM Nz(COUNT(*), 0) AS c
SELECT Sunday
FROM ...
GROUP BY Sunday
PIVOT Event



Here, the crosstab generates the columns A and B, for each group. It may
then happen that no event did occur, for a "B", so the count(*) would be
null. That is why we use Nz to get a 0, instead of the null, in that
circumstance.


Hoping it may help,
Vanderghast, Access MVP

 

[G Lykos]

Michel, haven't had a chance to experiment with this yet, but appreciated
the guidance.

George

COUNT(*) will not return 0, since that would mean there is no row in the
group, also as in no row to display that 0. (Crosstab query is an exception
since its rows are not generated in exactly the same context).

COUNT(fieldName) could return 0, though, if it happens that no initial row
has a not null value under the fieldName column, for the given group.

Anyhow, in your case, it seems you could try a crosstab:

TRANSFORM Nz(COUNT(*), 0) AS c
SELECT Sunday
FROM ...
GROUP BY Sunday
PIVOT Event



Here, the crosstab generates the columns A and B, for each group. It may
then happen that no event did occur, for a "B", so the count(*) would be
null. That is why we use Nz to get a 0, instead of the null, in that
circumstance.


Hoping it may help,
Vanderghast, Access MVP

Greetings! Could use a little orientation.

Given a table Events:

Event SomeDate
A 4/1/07
A 4/10/07
B 3/26/07

Want to count occurrences of the events per week ending on Sundays.

So for above, in the Sunday date range 4/1 - 4/15, result would be:

Sunday A B
4/1/07 1 1
4/8/07 0 0
4/15/07 1 0

Created a table Weeks:
Sunday
4/1/07
4/8/07
4/15/07

A first SQL query goes:
SELECT Sunday, COUNT (*)
FROM Weeks LEFT JOIN [Events] on (SomeDate) BETWEEN (Sunday-6) AND
(Sunday)
GROUP BY Sunday

Output is:
4/1/07 2
4/8/07 1
4/15/07 1

The 4/1 and 4/15 counts are true. Question 1: I'm picking up a null (I
think) on 4/8, which should be 0 rather than 1, but don't know how to
figure
out from where or what to do about it.

If I insert a WHERE:
SELECT Sunday, COUNT (*)
FROM Weeks LEFT JOIN [Events] on (SomeDate) BETWEEN (Sunday-6) AND
(Sunday)
WHERE (Event LIKE "A" OR Event LIKE "B")
GROUP BY Sunday

then I lose the empty week 4/8, with results:
4/1 2
4/15 1

Also, I tried:
SELECT Sunday, COUNT (Event LIKE "A"), COUNT (Event LIKE "B")
FROM Weeks LEFT JOIN [Weeks] on (SomeDate) BETWEEN (Sunday-6) AND (Sunday)
GROUP BY Sunday

hoping to get to:
Sunday A B
4/1/07 1 1
4/8/07 0 0
4/15/07 1 0

Question 2: While the syntax doesn't cause an error, the results of the
two
COUNT expressions are not affected in the least by their arguments, both
of
which yield the same counts as COUNT (*), so COUNT is apparently not
intended to be used in this fashion.

Would appreciate any guidance to the two questions, as well as suggested
course of action.

Thanks,
George