Access SQL - date sieve

[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

 

[Allen Browne]

In query design view, type this into a fresh column in the Field row:
WeekStarting: CVDate([SomeDate] - Weekday([SomeDate]) + 1)

You can now group by this field in your totals query, and count the Event
field.

If you want the events as column headings, change the query to a Crosstab
(Crosstab on Query menu). Use:
- WeekStarting (group by) as Row Heading
- Event (group by) as Column Heading
- Event (count) as Value.

 

[G Lykos]

Allen, thanks for the suggestion of using a calculated field to augment the
records, pegging the beginning (or ending, as I use it) of the week in each.

How might I then use this to generate a crosstab listing of all weeks,
including those with zero event counts?

Thanks again,
George

In query design view, type this into a fresh column in the Field row:
WeekStarting: CVDate([SomeDate] - Weekday([SomeDate]) + 1)

You can now group by this field in your totals query, and count the Event
field.

If you want the events as column headings, change the query to a Crosstab
(Crosstab on Query menu). Use:
- WeekStarting (group by) as Row Heading
- Event (group by) as Column Heading
- Event (count) as Value.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

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

 

[Allen Browne]

If there are no records, the data has to come from somewhere.

You will therefore need to create a table of the week-ending dates (one
record per week), and outer-join that to your crosstab so it gives you every
week.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen, thanks for the suggestion of using a calculated field to augment
the
records, pegging the beginning (or ending, as I use it) of the week in
each.

How might I then use this to generate a crosstab listing of all weeks,
including those with zero event counts?

Thanks again,
George

In query design view, type this into a fresh column in the Field row:
WeekStarting: CVDate([SomeDate] - Weekday([SomeDate]) + 1)

You can now group by this field in your totals query, and count the Event
field.

If you want the events as column headings, change the query to a Crosstab
(Crosstab on Query menu). Use:
- WeekStarting (group by) as Row Heading
- Event (group by) as Column Heading
- Event (count) as Value.

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.

 

[G Lykos]

Okay, Allen, so that was the general structure I outlined in my initial
post.

Having now augmented the data table Open with a calculated CREATE_WE
(WeekEnding) field and created a pivot table per your suggestion, I have the
following that works very nicely, thank you (for those weeks with counts):

TRANSFORM Count(Open.CREATE_DATE) AS CountOfCREATE_DATE
SELECT Open.CREATE_WE, Count(Open.CREATE_DATE) AS [Total of CREATE_DATE]
FROM [Open]
GROUP BY Open.CREATE_WE
PIVOT Open.Team;

So now, bringing in ancillary file Weeks, having a list of all (WeekEnding)
Sunday dates in field Sunday corresponding to CREATE_WE above, how do I
outer-join it to this pivot-table query such that all weeks appear for any
counts 0 to n?

Thanks again,
George

If there are no records, the data has to come from somewhere.

You will therefore need to create a table of the week-ending dates (one
record per week), and outer-join that to your crosstab so it gives you every
week.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen, thanks for the suggestion of using a calculated field to augment
the
records, pegging the beginning (or ending, as I use it) of the week in
each.

How might I then use this to generate a crosstab listing of all weeks,
including those with zero event counts?

Thanks again,
George

In query design view, type this into a fresh column in the Field row:
WeekStarting: CVDate([SomeDate] - Weekday([SomeDate]) + 1)

You can now group by this field in your totals query, and count the Event
field.

If you want the events as column headings, change the query to a Crosstab
(Crosstab on Query menu). Use:
- WeekStarting (group by) as Row Heading
- Event (group by) as Column Heading
- Event (count) as Value.

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.

 

[Allen Browne]

Perhaps you could just use a counting table (one Number field named
TheCount; records 0, 1, ... n). Then use the field:
DateAdd("ww", [TheCount], [StartingDate])
to generate the weekly dates.
Then outer-join that to the crosstab.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Okay, Allen, so that was the general structure I outlined in my initial
post.

Having now augmented the data table Open with a calculated CREATE_WE
(WeekEnding) field and created a pivot table per your suggestion, I have
the
following that works very nicely, thank you (for those weeks with counts):

TRANSFORM Count(Open.CREATE_DATE) AS CountOfCREATE_DATE
SELECT Open.CREATE_WE, Count(Open.CREATE_DATE) AS [Total of CREATE_DATE]
FROM [Open]
GROUP BY Open.CREATE_WE
PIVOT Open.Team;

So now, bringing in ancillary file Weeks, having a list of all
(WeekEnding)
Sunday dates in field Sunday corresponding to CREATE_WE above, how do I
outer-join it to this pivot-table query such that all weeks appear for any
counts 0 to n?

Thanks again,
George

If there are no records, the data has to come from somewhere.

You will therefore need to create a table of the week-ending dates (one
record per week), and outer-join that to your crosstab so it gives you every
week.

Allen, thanks for the suggestion of using a calculated field to augment
the
records, pegging the beginning (or ending, as I use it) of the week in
each.

How might I then use this to generate a crosstab listing of all weeks,
including those with zero event counts?

In query design view, type this into a fresh column in the Field row:
WeekStarting: CVDate([SomeDate] - Weekday([SomeDate]) + 1)

You can now group by this field in your totals query, and count the Event
field.

If you want the events as column headings, change the query to a Crosstab
(Crosstab on Query menu). Use:
- WeekStarting (group by) as Row Heading
- Event (group by) as Column Heading
- Event (count) as Value.

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.

 

[G Lykos]

Allen, generating the date table is not the problem - how to outer-join one
to the pivot-table query is my challenge. For grins, I tried the GUI
approach, adding the date table to the query and then linking its Sunday
field to Open.CREATE_WE - the resulting query stalls on a null error. Is
there a GUI method, or would you know how to modify the pivot-table SQL
below to outer-join the date table?

Thanks,
George

Perhaps you could just use a counting table (one Number field named
TheCount; records 0, 1, ... n). Then use the field:
DateAdd("ww", [TheCount], [StartingDate])
to generate the weekly dates.
Then outer-join that to the crosstab.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Okay, Allen, so that was the general structure I outlined in my initial
post.

Having now augmented the data table Open with a calculated CREATE_WE
(WeekEnding) field and created a pivot table per your suggestion, I have
the
following that works very nicely, thank you (for those weeks with counts):

TRANSFORM Count(Open.CREATE_DATE) AS CountOfCREATE_DATE
SELECT Open.CREATE_WE, Count(Open.CREATE_DATE) AS [Total of CREATE_DATE]
FROM [Open]
GROUP BY Open.CREATE_WE
PIVOT Open.Team;

So now, bringing in ancillary file Weeks, having a list of all
(WeekEnding)
Sunday dates in field Sunday corresponding to CREATE_WE above, how do I
outer-join it to this pivot-table query such that all weeks appear for any
counts 0 to n?

Thanks again,
George

If there are no records, the data has to come from somewhere.

You will therefore need to create a table of the week-ending dates (one
record per week), and outer-join that to your crosstab so it gives you every
week.

Allen, thanks for the suggestion of using a calculated field to augment
the
records, pegging the beginning (or ending, as I use it) of the week in
each.

How might I then use this to generate a crosstab listing of all weeks,
including those with zero event counts?

In query design view, type this into a fresh column in the Field row:
WeekStarting: CVDate([SomeDate] - Weekday([SomeDate]) + 1)

You can now group by this field in your totals query, and count the Event
field.

If you want the events as column headings, change the query to a Crosstab
(Crosstab on Query menu). Use:
- WeekStarting (group by) as Row Heading
- Event (group by) as Column Heading
- Event (count) as Value.

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.

 

[Allen Browne]

Simplest way would be to use the crosstab query as in input "table" for
another query.

1. Create a new query.

2. Add the counter/date table, and the crosstab query as source tables.

3. In the upper pane of the query design window, drag the date field from
one table onto the date field of the other.
Access displays a join line.

4. Double-click the join line.
Access pops up a dialog with 3 choices.
Choose the one that says:
All records from {whatever your counter table is called},
and any matches from the other table.

Hope I've understood your question.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen, generating the date table is not the problem - how to outer-join
one
to the pivot-table query is my challenge. For grins, I tried the GUI
approach, adding the date table to the query and then linking its Sunday
field to Open.CREATE_WE - the resulting query stalls on a null error. Is
there a GUI method, or would you know how to modify the pivot-table SQL
below to outer-join the date table?

Thanks,
George

Perhaps you could just use a counting table (one Number field named
TheCount; records 0, 1, ... n). Then use the field:
DateAdd("ww", [TheCount], [StartingDate])
to generate the weekly dates.
Then outer-join that to the crosstab.

Okay, Allen, so that was the general structure I outlined in my initial
post.

Having now augmented the data table Open with a calculated CREATE_WE
(WeekEnding) field and created a pivot table per your suggestion, I
have
the
following that works very nicely, thank you (for those weeks with counts):

TRANSFORM Count(Open.CREATE_DATE) AS CountOfCREATE_DATE
SELECT Open.CREATE_WE, Count(Open.CREATE_DATE) AS [Total of
CREATE_DATE]
FROM [Open]
GROUP BY Open.CREATE_WE
PIVOT Open.Team;

So now, bringing in ancillary file Weeks, having a list of all
(WeekEnding)
Sunday dates in field Sunday corresponding to CREATE_WE above, how do I
outer-join it to this pivot-table query such that all weeks appear for any
counts 0 to n?

Thanks again,
George


If there are no records, the data has to come from somewhere.

You will therefore need to create a table of the week-ending dates
(one
record per week), and outer-join that to your crosstab so it gives you
every
week.

Allen, thanks for the suggestion of using a calculated field to augment
the
records, pegging the beginning (or ending, as I use it) of the week in
each.

How might I then use this to generate a crosstab listing of all weeks,
including those with zero event counts?

In query design view, type this into a fresh column in the Field row:
WeekStarting: CVDate([SomeDate] - Weekday([SomeDate]) + 1)

You can now group by this field in your totals query, and count the
Event
field.

If you want the events as column headings, change the query to a
Crosstab
(Crosstab on Query menu). Use:
- WeekStarting (group by) as Row Heading
- Event (group by) as Column Heading
- Event (count) as Value.

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.