Count number of Appointments w/in 7 Day Window

[Rod]

I have tblCalendar (an export for Outlook). I am trying to find the number
of appointments within a 7 day window based on the start date of the
appointment. I am converting the start date by Start Date:
CDate([StartDate]). The query is bring back the apporpriate record based on
"Category" criteria and ">=Date() AND <=Date()+7", however, I would like to
have "KT" be the number of appointments scheduled to take place on
CDate([StartDate]). I tried several approaches but always get strange
numbers such as 395 appointment on a given day when using: KTs:
DCount((FormatDateTime(CDate([StartDate]),0)),"qCalendar",FormatDateTime(CDate([StartDate]),0)>=Date())
in my query. (qCalendar filters out all non-biz related appointments from
tblCalendar.)

Thanks for your help.

 

[Allen Browne]

Use a subquery.

For each date that you have an appointment, this query gives you the number
of appointments in the coming 7 days:

SELECT DateValue([tblCalendar].[StartDate]) AS TheDate,
(SELECT Count([AppointmentID]) AS TheCount
FROM tblCalendar AS Dupe
WHERE Dupe.StartDate >= DateValue([tblCalendar].[StartDate])
AND Dupe.StartDate < DateValue([tblCalendar].[StartDate]) + 8)
AS WeekAheadCount
FROM tblCalendar
GROUP BY DateValue([StartDate]);

If subqueries are new, see:
http://allenbrowne.com/subquery-01.html

 

[Rod]

Hi Allen,

It looks like you are close. I am getting a type mismatch. I think the
disconnect is in the "WeekAheadCount" statement. You are using
[AppointmentID]. My intention was to count how many appointments, so I was
trying to count the number of times qCalendar brought back any value for
Categories w/in the 7d window. There are several valid values for Categories
which qCalendar is handling perfectly. But Categories is a text field. Does
this help?

Use a subquery.

For each date that you have an appointment, this query gives you the number
of appointments in the coming 7 days:

SELECT DateValue([tblCalendar].[StartDate]) AS TheDate,
(SELECT Count([AppointmentID]) AS TheCount
FROM tblCalendar AS Dupe
WHERE Dupe.StartDate >= DateValue([tblCalendar].[StartDate])
AND Dupe.StartDate < DateValue([tblCalendar].[StartDate]) + 8)
AS WeekAheadCount
FROM tblCalendar
GROUP BY DateValue([StartDate]);

If subqueries are new, see:
http://allenbrowne.com/subquery-01.html

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

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

I have tblCalendar (an export for Outlook). I am trying to find the number
of appointments within a 7 day window based on the start date of the
appointment. I am converting the start date by Start Date:
CDate([StartDate]). The query is bring back the apporpriate record based
on
"Category" criteria and ">=Date() AND <=Date()+7", however, I would like
to
have "KT" be the number of appointments scheduled to take place on
CDate([StartDate]). I tried several approaches but always get strange
numbers such as 395 appointment on a given day when using: KTs:
DCount((FormatDateTime(CDate([StartDate]),0)),"qCalendar",FormatDateTime(CDate([StartDate]),0)>=Date())
in my query. (qCalendar filters out all non-biz related appointments from
tblCalendar.)

Thanks for your help.

 

[Allen Browne]

You can count any field in the subquery, regardless of its data type.

A type mismatch could occur of StartDate is not a date/time field (e.g. if
it is a Text field.)

If you can't get it working all at once, you may be able to break it down
into parts to see which bit is giving you the error.

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

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

Hi Allen,

It looks like you are close. I am getting a type mismatch. I think the
disconnect is in the "WeekAheadCount" statement. You are using
[AppointmentID]. My intention was to count how many appointments, so I
was
trying to count the number of times qCalendar brought back any value for
Categories w/in the 7d window. There are several valid values for
Categories
which qCalendar is handling perfectly. But Categories is a text field.
Does
this help?

Use a subquery.

For each date that you have an appointment, this query gives you the
number
of appointments in the coming 7 days:

SELECT DateValue([tblCalendar].[StartDate]) AS TheDate,
(SELECT Count([AppointmentID]) AS TheCount
FROM tblCalendar AS Dupe
WHERE Dupe.StartDate >= DateValue([tblCalendar].[StartDate])
AND Dupe.StartDate < DateValue([tblCalendar].[StartDate]) + 8)
AS WeekAheadCount
FROM tblCalendar
GROUP BY DateValue([StartDate]);

If subqueries are new, see:
http://allenbrowne.com/subquery-01.html

I have tblCalendar (an export for Outlook). I am trying to find the
number
of appointments within a 7 day window based on the start date of the
appointment. I am converting the start date by Start Date:
CDate([StartDate]). The query is bring back the apporpriate record
based
on
"Category" criteria and ">=Date() AND <=Date()+7", however, I would
like
to
have "KT" be the number of appointments scheduled to take place on
CDate([StartDate]). I tried several approaches but always get strange
numbers such as 395 appointment on a given day when using: KTs:
DCount((FormatDateTime(CDate([StartDate]),0)),"qCalendar",FormatDateTime(CDate([StartDate]),0)>=Date())
in my query. (qCalendar filters out all non-biz related appointments
from
tblCalendar.)

 

[Rod]

The result of:
SELECT DateValue([StartDate]) AS TheDate, (SELECT Count([Categories]) AS
TheCount FROM qCalendar AS Dupe WHERE Dupe.StartDate >=
DateValue([StartDate]) AND Dupe.StartDate < DateValue([StartDate]) + 8) AS
WeekAheadCount
FROM qCalendar
GROUP BY DateValue([StartDate]);

gives me two col. The first col, TheDate, gives me the dates retrieved via
qCalendar - works fine. The second col, WeekAheadCount, is telling me I have
269 {appointments?} for each date. I'm not sure what the 269 is, but I was
expecting to see the number of appointments per date. It also does not
appear to be giving a 7 day window. qCalendar is from 1/1/2007 to 7/1/2007
(this query will be used for other things). I was expecting the query we are
working on to narrow that down to a 7 day window and count the number of
appointments per day. It seems like we are getting closer.

Thanks for your continued support.

Use a subquery.

For each date that you have an appointment, this query gives you the number
of appointments in the coming 7 days:

SELECT DateValue([tblCalendar].[StartDate]) AS TheDate,
(SELECT Count([AppointmentID]) AS TheCount
FROM tblCalendar AS Dupe
WHERE Dupe.StartDate >= DateValue([tblCalendar].[StartDate])
AND Dupe.StartDate < DateValue([tblCalendar].[StartDate]) + 8)
AS WeekAheadCount
FROM tblCalendar
GROUP BY DateValue([StartDate]);

If subqueries are new, see:
http://allenbrowne.com/subquery-01.html

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

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

I have tblCalendar (an export for Outlook). I am trying to find the number
of appointments within a 7 day window based on the start date of the
appointment. I am converting the start date by Start Date:
CDate([StartDate]). The query is bring back the apporpriate record based
on
"Category" criteria and ">=Date() AND <=Date()+7", however, I would like
to
have "KT" be the number of appointments scheduled to take place on
CDate([StartDate]). I tried several approaches but always get strange
numbers such as 395 appointment on a given day when using: KTs:
DCount((FormatDateTime(CDate([StartDate]),0)),"qCalendar",FormatDateTime(CDate([StartDate]),0)>=Date())
in my query. (qCalendar filters out all non-biz related appointments from
tblCalendar.)

Thanks for your help.

 

[Allen Browne]

It should give you a 7-day window: greater than or equal to the appointment
date, and less than the appointment date + 8 days -- that's a 7 day period.

I guess something else must be going on, such as not narrowing to the
desired person, or a confusion of data types (i.e. the fields are not real
date/time fields, or they are calculated fields Access is confused about.)

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

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

The result of:
SELECT DateValue([StartDate]) AS TheDate, (SELECT Count([Categories]) AS
TheCount FROM qCalendar AS Dupe WHERE Dupe.StartDate >=
DateValue([StartDate]) AND Dupe.StartDate < DateValue([StartDate]) + 8) AS
WeekAheadCount
FROM qCalendar
GROUP BY DateValue([StartDate]);

gives me two col. The first col, TheDate, gives me the dates retrieved
via
qCalendar - works fine. The second col, WeekAheadCount, is telling me I
have
269 {appointments?} for each date. I'm not sure what the 269 is, but I
was
expecting to see the number of appointments per date. It also does not
appear to be giving a 7 day window. qCalendar is from 1/1/2007 to
7/1/2007
(this query will be used for other things). I was expecting the query we
are
working on to narrow that down to a 7 day window and count the number of
appointments per day. It seems like we are getting closer.

Thanks for your continued support.

Use a subquery.

For each date that you have an appointment, this query gives you the
number
of appointments in the coming 7 days:

SELECT DateValue([tblCalendar].[StartDate]) AS TheDate,
(SELECT Count([AppointmentID]) AS TheCount
FROM tblCalendar AS Dupe
WHERE Dupe.StartDate >= DateValue([tblCalendar].[StartDate])
AND Dupe.StartDate < DateValue([tblCalendar].[StartDate]) + 8)
AS WeekAheadCount
FROM tblCalendar
GROUP BY DateValue([StartDate]);

If subqueries are new, see:
http://allenbrowne.com/subquery-01.html

I have tblCalendar (an export for Outlook). I am trying to find the
number
of appointments within a 7 day window based on the start date of the
appointment. I am converting the start date by Start Date:
CDate([StartDate]). The query is bring back the apporpriate record
based
on
"Category" criteria and ">=Date() AND <=Date()+7", however, I would
like
to
have "KT" be the number of appointments scheduled to take place on
CDate([StartDate]). I tried several approaches but always get strange
numbers such as 395 appointment on a given day when using: KTs:
DCount((FormatDateTime(CDate([StartDate]),0)),"qCalendar",FormatDateTime(CDate([StartDate]),0)>=Date())
in my query. (qCalendar filters out all non-biz related appointments
from
tblCalendar.)

 

[Rod]

The fields all get to my database as text, thus the DateValue function. But
why the 269 value? If I could even get the number of appointment per day for
every day returned by qCalendar I could get by just fine by changing
qCalendar to a 7 day window instead of a 6 month window. So, the main
problem is counting the number of appointments per day.

Thoughts?

It should give you a 7-day window: greater than or equal to the appointment
date, and less than the appointment date + 8 days -- that's a 7 day period.

I guess something else must be going on, such as not narrowing to the
desired person, or a confusion of data types (i.e. the fields are not real
date/time fields, or they are calculated fields Access is confused about.)

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

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

The result of:
SELECT DateValue([StartDate]) AS TheDate, (SELECT Count([Categories]) AS
TheCount FROM qCalendar AS Dupe WHERE Dupe.StartDate >=
DateValue([StartDate]) AND Dupe.StartDate < DateValue([StartDate]) + 8) AS
WeekAheadCount
FROM qCalendar
GROUP BY DateValue([StartDate]);

gives me two col. The first col, TheDate, gives me the dates retrieved
via
qCalendar - works fine. The second col, WeekAheadCount, is telling me I
have
269 {appointments?} for each date. I'm not sure what the 269 is, but I
was
expecting to see the number of appointments per date. It also does not
appear to be giving a 7 day window. qCalendar is from 1/1/2007 to
7/1/2007
(this query will be used for other things). I was expecting the query we
are
working on to narrow that down to a 7 day window and count the number of
appointments per day. It seems like we are getting closer.

Thanks for your continued support.

Use a subquery.

For each date that you have an appointment, this query gives you the
number
of appointments in the coming 7 days:

SELECT DateValue([tblCalendar].[StartDate]) AS TheDate,
(SELECT Count([AppointmentID]) AS TheCount
FROM tblCalendar AS Dupe
WHERE Dupe.StartDate >= DateValue([tblCalendar].[StartDate])
AND Dupe.StartDate < DateValue([tblCalendar].[StartDate]) + 8)
AS WeekAheadCount
FROM tblCalendar
GROUP BY DateValue([StartDate]);

If subqueries are new, see:
http://allenbrowne.com/subquery-01.html

I have tblCalendar (an export for Outlook). I am trying to find the
number
of appointments within a 7 day window based on the start date of the
appointment. I am converting the start date by Start Date:
CDate([StartDate]). The query is bring back the apporpriate record
based
on
"Category" criteria and ">=Date() AND <=Date()+7", however, I would
like
to
have "KT" be the number of appointments scheduled to take place on
CDate([StartDate]). I tried several approaches but always get strange
numbers such as 395 appointment on a given day when using: KTs:
DCount((FormatDateTime(CDate([StartDate]),0)),"qCalendar",FormatDateTime(CDate([StartDate]),0)>=Date())
in my query. (qCalendar filters out all non-biz related appointments
from
tblCalendar.)

 

[Allen Browne]

Ah: I assumed you were trying to lose the time component.
Try CVDate() instead of DateValue, around *every* instance of StartDate.

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

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

The fields all get to my database as text, thus the DateValue function.
But
why the 269 value? If I could even get the number of appointment per day
for
every day returned by qCalendar I could get by just fine by changing
qCalendar to a 7 day window instead of a 6 month window. So, the main
problem is counting the number of appointments per day.

Thoughts?

It should give you a 7-day window: greater than or equal to the
appointment
date, and less than the appointment date + 8 days -- that's a 7 day
period.

I guess something else must be going on, such as not narrowing to the
desired person, or a confusion of data types (i.e. the fields are not
real
date/time fields, or they are calculated fields Access is confused
about.)

The result of:
SELECT DateValue([StartDate]) AS TheDate, (SELECT Count([Categories])
AS
TheCount FROM qCalendar AS Dupe WHERE Dupe.StartDate >=
DateValue([StartDate]) AND Dupe.StartDate < DateValue([StartDate]) + 8)
AS
WeekAheadCount
FROM qCalendar
GROUP BY DateValue([StartDate]);

gives me two col. The first col, TheDate, gives me the dates retrieved
via
qCalendar - works fine. The second col, WeekAheadCount, is telling me
I
have
269 {appointments?} for each date. I'm not sure what the 269 is, but I
was
expecting to see the number of appointments per date. It also does not
appear to be giving a 7 day window. qCalendar is from 1/1/2007 to
7/1/2007
(this query will be used for other things). I was expecting the query
we
are
working on to narrow that down to a 7 day window and count the number
of
appointments per day. It seems like we are getting closer.

Thanks for your continued support.

:

Use a subquery.

For each date that you have an appointment, this query gives you the
number
of appointments in the coming 7 days:

SELECT DateValue([tblCalendar].[StartDate]) AS TheDate,
(SELECT Count([AppointmentID]) AS TheCount
FROM tblCalendar AS Dupe
WHERE Dupe.StartDate >= DateValue([tblCalendar].[StartDate])
AND Dupe.StartDate < DateValue([tblCalendar].[StartDate]) + 8)
AS WeekAheadCount
FROM tblCalendar
GROUP BY DateValue([StartDate]);

If subqueries are new, see:
http://allenbrowne.com/subquery-01.html

I have tblCalendar (an export for Outlook). I am trying to find the
number
of appointments within a 7 day window based on the start date of the
appointment. I am converting the start date by Start Date:
CDate([StartDate]). The query is bring back the apporpriate record
based
on
"Category" criteria and ">=Date() AND <=Date()+7", however, I would
like
to
have "KT" be the number of appointments scheduled to take place on
CDate([StartDate]). I tried several approaches but always get
strange
numbers such as 395 appointment on a given day when using: KTs:
DCount((FormatDateTime(CDate([StartDate]),0)),"qCalendar",FormatDateTime(CDate([StartDate]),0)>=Date())
in my query. (qCalendar filters out all non-biz related
appointments
from tblCalendar.)

 

[Rod]

Your assumption is correct. I do not need the time component. Let me give
you a clearer picture. I am exporting my Outlook calendar to an Access
database. qCalendar gives me the appropriate information based on Categories:

SELECT tblCalendar.StartDate, tblCalendar.StartTime, tblCalendar.EndDate,
tblCalendar.EndTime, tblCalendar.Categories, tblCalendar.Subject,
tblCalendar.Description, tblCalendar.Location
FROM tblCalendar
WHERE (((tblCalendar.Categories)<>"Personal" And
(tblCalendar.Categories)<>"Holiday" And (tblCalendar.Categories)<>"Birthday"
And (tblCalendar.Categories)<>"Phone Calls" And
(tblCalendar.Categories)<>"Business"));

qCalendar returns data that looks like:

StartDate Categories Subject StartTime EndDate EndTime Description Location
1/3/2007 Recruiting Int: Marc Weinstien 1 ### ### #### 1:00:00
PM 1/3/2007 2:00:00 PM DF
1/3/2007 Recruiting Int: Greg Perc. 1 ### ### #### 2:30:00
PM 1/3/2007 3:30:00 PM "From Lake Villa
" DF
1/2/2007 Securities Mark J. 6:00:00 PM 1/2/2007 6:45:00 PM "Rollover
$114 from Hos.
$ 12K from bank
" Gur

{sorry for the long lines}

I am looking for qKT test to:
1) bring back a 7 day window of appointments for qCalendar
2) return a value KT (appointments) which are the total appointments set
during the 7 day window.

You see, the idea is a report can eventually be printed that answers the
question, "How many appointments do I have over the next 7 days?"

Right now qKT test looks like:

SELECT CVDate([StartDate]) AS TheDate, (SELECT Count([Categories]) AS
TheCount FROM qCalendar AS Dupe WHERE Dupe.StartDate >=
CVDate([StartDate]) AND Dupe.StartDate < CVDate([StartDate]) + 8) AS
WeekAheadCount
FROM qCalendar
GROUP BY CVDate([StartDate]);

and returns 70 lines that look like:

TheDate WeekAheadCount
1/2/2007 92
1/3/2007 92
1/4/2007 92

 

[Allen Browne]

Re-reading this thread, I see you said:
I'm not sure what the 269 is, but I was expecting to see
the number of appointments per date.

The 269 is the total number of appointments in the 7 days ahead.
It seems you want a *daily* count for each of the 7 days, and not just a
total? If so, that will need a different approach.

If your goal is to list all the appointments for each of the next 7 days in
a report, you don't want any grouping or counting at all. Just add criteria
under your date field in query design limiting it to:

= Date() And < DateAdd("d", 8, Date())

The query give you each appointment in the week ahead. You can now create
the report grouping by the date field (each day) to show the appointments.

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

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

Your assumption is correct. I do not need the time component. Let me
give
you a clearer picture. I am exporting my Outlook calendar to an Access
database. qCalendar gives me the appropriate information based on
Categories:

SELECT tblCalendar.StartDate, tblCalendar.StartTime, tblCalendar.EndDate,
tblCalendar.EndTime, tblCalendar.Categories, tblCalendar.Subject,
tblCalendar.Description, tblCalendar.Location
FROM tblCalendar
WHERE (((tblCalendar.Categories)<>"Personal" And
(tblCalendar.Categories)<>"Holiday" And
(tblCalendar.Categories)<>"Birthday"
And (tblCalendar.Categories)<>"Phone Calls" And
(tblCalendar.Categories)<>"Business"));

qCalendar returns data that looks like:

StartDate Categories Subject StartTime EndDate EndTime Description
Location
1/3/2007 Recruiting Int: Marc Weinstien 1 ### ### #### 1:00:00
PM 1/3/2007 2:00:00 PM DF
1/3/2007 Recruiting Int: Greg Perc. 1 ### ### #### 2:30:00
PM 1/3/2007 3:30:00 PM "From Lake Villa
" DF
1/2/2007 Securities Mark J. 6:00:00 PM 1/2/2007 6:45:00 PM "Rollover
$114 from Hos.
$ 12K from bank
" Gur

{sorry for the long lines}

I am looking for qKT test to:
1) bring back a 7 day window of appointments for qCalendar
2) return a value KT (appointments) which are the total appointments set
during the 7 day window.

You see, the idea is a report can eventually be printed that answers the
question, "How many appointments do I have over the next 7 days?"

Right now qKT test looks like:

SELECT CVDate([StartDate]) AS TheDate, (SELECT Count([Categories]) AS
TheCount FROM qCalendar AS Dupe WHERE Dupe.StartDate >=
CVDate([StartDate]) AND Dupe.StartDate < CVDate([StartDate]) + 8) AS
WeekAheadCount
FROM qCalendar
GROUP BY CVDate([StartDate]);

and returns 70 lines that look like:

TheDate WeekAheadCount
1/2/2007 92
1/3/2007 92
1/4/2007 92
.
.
.
4/1/2007 92
4/3/2007 92
4/6/2007 92
4/7/2007 92
4/9/2007 92
4/10/2007 92
4/12/2007 92

So, it is not giving me the 7d window, and I have no idea what it means by
WeekAheadCount being 92.

I know this is a lot of info, but now you have everything. Hopefully the
problem was not simply insufficient data.

Thanks for helping!

Ah: I assumed you were trying to lose the time component.
Try CVDate() instead of DateValue, around *every* instance of StartDate.

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

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

The fields all get to my database as text, thus the DateValue function.
But
why the 269 value? If I could even get the number of appointment per
day
for
every day returned by qCalendar I could get by just fine by changing
qCalendar to a 7 day window instead of a 6 month window. So, the main
problem is counting the number of appointments per day.

Thoughts?

:

It should give you a 7-day window: greater than or equal to the
appointment
date, and less than the appointment date + 8 days -- that's a 7 day
period.

I guess something else must be going on, such as not narrowing to the
desired person, or a confusion of data types (i.e. the fields are not
real
date/time fields, or they are calculated fields Access is confused
about.)

The result of:
SELECT DateValue([StartDate]) AS TheDate, (SELECT
Count([Categories])
AS
TheCount FROM qCalendar AS Dupe WHERE Dupe.StartDate >=
DateValue([StartDate]) AND Dupe.StartDate < DateValue([StartDate]) +
8)
AS
WeekAheadCount
FROM qCalendar
GROUP BY DateValue([StartDate]);

gives me two col. The first col, TheDate, gives me the dates
retrieved
via
qCalendar - works fine. The second col, WeekAheadCount, is telling
me
I
have
269 {appointments?} for each date. I'm not sure what the 269 is,
but I
was
expecting to see the number of appointments per date. It also does
not
appear to be giving a 7 day window. qCalendar is from 1/1/2007 to
7/1/2007
(this query will be used for other things). I was expecting the
query
we
are
working on to narrow that down to a 7 day window and count the
number
of
appointments per day. It seems like we are getting closer.

Thanks for your continued support.

:

Use a subquery.

For each date that you have an appointment, this query gives you
the
number
of appointments in the coming 7 days:

SELECT DateValue([tblCalendar].[StartDate]) AS TheDate,
(SELECT Count([AppointmentID]) AS TheCount
FROM tblCalendar AS Dupe
WHERE Dupe.StartDate >= DateValue([tblCalendar].[StartDate])
AND Dupe.StartDate < DateValue([tblCalendar].[StartDate]) + 8)
AS WeekAheadCount
FROM tblCalendar
GROUP BY DateValue([StartDate]);

If subqueries are new, see:
http://allenbrowne.com/subquery-01.html

I have tblCalendar (an export for Outlook). I am trying to find
the
number
of appointments within a 7 day window based on the start date of
the
appointment. I am converting the start date by Start Date:
CDate([StartDate]). The query is bring back the apporpriate
record
based
on
"Category" criteria and ">=Date() AND <=Date()+7", however, I
would
like
to
have "KT" be the number of appointments scheduled to take place
on
CDate([StartDate]). I tried several approaches but always get
strange
numbers such as 395 appointment on a given day when using: KTs:
DCount((FormatDateTime(CDate([StartDate]),0)),"qCalendar",FormatDateTime(CDate([StartDate]),0)>=Date())
in my query. (qCalendar filters out all non-biz related
appointments
from tblCalendar.)

 

[Rod]

I made the changes. The query looks like:

SELECT CVDate([StartDate]) AS TheDate, (SELECT Count([Categories]) AS
TheCount FROM qCalendar AS Dupe WHERE Dupe.StartDate >=
CVDate([StartDate]) AND Dupe.StartDate < CVDate([StartDate]) + 8) AS
WeekAheadCount
FROM qCalendar
GROUP BY CVDate([StartDate])
HAVING (((CVDate([StartDate]))>=Date() And
(CVDate([StartDate]))<DateAdd("d",8,Date())));

The result looks like:

TheDate WeekAheadCount
4/9/2007 95
4/10/2007 95
4/12/2007 95
4/13/2007 95
4/14/2007 95

We will ned the current dat included in the window, but if I am reading this
right, it is telling me I have 95 appointments on each day. Here is what the
actual result should be for the next 7 days including today:

TheDate WeekAheadCount
4/8/2007 0
4/9/2007 2
4/10/2007 1
4/11/2007 0
4/12/2007 2
4/13/2007 0
4/14/2007 0

This will allow me to run a report that will result in something like:
.... 7d KTs
.... 5

Thanks for your support!

Re-reading this thread, I see you said:
I'm not sure what the 269 is, but I was expecting to see
the number of appointments per date.

The 269 is the total number of appointments in the 7 days ahead.
It seems you want a *daily* count for each of the 7 days, and not just a
total? If so, that will need a different approach.

If your goal is to list all the appointments for each of the next 7 days in
a report, you don't want any grouping or counting at all. Just add criteria
under your date field in query design limiting it to:

= Date() And < DateAdd("d", 8, Date())

The query give you each appointment in the week ahead. You can now create
the report grouping by the date field (each day) to show the appointments.

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

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

Your assumption is correct. I do not need the time component. Let me
give
you a clearer picture. I am exporting my Outlook calendar to an Access
database. qCalendar gives me the appropriate information based on
Categories:

SELECT tblCalendar.StartDate, tblCalendar.StartTime, tblCalendar.EndDate,
tblCalendar.EndTime, tblCalendar.Categories, tblCalendar.Subject,
tblCalendar.Description, tblCalendar.Location
FROM tblCalendar
WHERE (((tblCalendar.Categories)<>"Personal" And
(tblCalendar.Categories)<>"Holiday" And
(tblCalendar.Categories)<>"Birthday"
And (tblCalendar.Categories)<>"Phone Calls" And
(tblCalendar.Categories)<>"Business"));

qCalendar returns data that looks like:

StartDate Categories Subject StartTime EndDate EndTime Description
Location
1/3/2007 Recruiting Int: Marc Weinstien 1 ### ### #### 1:00:00
PM 1/3/2007 2:00:00 PM DF
1/3/2007 Recruiting Int: Greg Perc. 1 ### ### #### 2:30:00
PM 1/3/2007 3:30:00 PM "From Lake Villa
" DF
1/2/2007 Securities Mark J. 6:00:00 PM 1/2/2007 6:45:00 PM "Rollover
$114 from Hos.
$ 12K from bank
" Gur

{sorry for the long lines}

I am looking for qKT test to:
1) bring back a 7 day window of appointments for qCalendar
2) return a value KT (appointments) which are the total appointments set
during the 7 day window.

You see, the idea is a report can eventually be printed that answers the
question, "How many appointments do I have over the next 7 days?"

Right now qKT test looks like:

SELECT CVDate([StartDate]) AS TheDate, (SELECT Count([Categories]) AS
TheCount FROM qCalendar AS Dupe WHERE Dupe.StartDate >=
CVDate([StartDate]) AND Dupe.StartDate < CVDate([StartDate]) + 8) AS
WeekAheadCount
FROM qCalendar
GROUP BY CVDate([StartDate]);

and returns 70 lines that look like:

TheDate WeekAheadCount
1/2/2007 92
1/3/2007 92
1/4/2007 92
.
.
.
4/1/2007 92
4/3/2007 92
4/6/2007 92
4/7/2007 92
4/9/2007 92
4/10/2007 92
4/12/2007 92

So, it is not giving me the 7d window, and I have no idea what it means by
WeekAheadCount being 92.

I know this is a lot of info, but now you have everything. Hopefully the
problem was not simply insufficient data.

Thanks for helping!

Ah: I assumed you were trying to lose the time component.
Try CVDate() instead of DateValue, around *every* instance of StartDate.

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

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

The fields all get to my database as text, thus the DateValue function.
But
why the 269 value? If I could even get the number of appointment per
day
for
every day returned by qCalendar I could get by just fine by changing
qCalendar to a 7 day window instead of a 6 month window. So, the main
problem is counting the number of appointments per day.

Thoughts?

:

It should give you a 7-day window: greater than or equal to the
appointment
date, and less than the appointment date + 8 days -- that's a 7 day
period.

I guess something else must be going on, such as not narrowing to the
desired person, or a confusion of data types (i.e. the fields are not
real
date/time fields, or they are calculated fields Access is confused
about.)

The result of:
SELECT DateValue([StartDate]) AS TheDate, (SELECT
Count([Categories])
AS
TheCount FROM qCalendar AS Dupe WHERE Dupe.StartDate >=
DateValue([StartDate]) AND Dupe.StartDate < DateValue([StartDate]) +
8)
AS
WeekAheadCount
FROM qCalendar
GROUP BY DateValue([StartDate]);

gives me two col. The first col, TheDate, gives me the dates
retrieved
via
qCalendar - works fine. The second col, WeekAheadCount, is telling
me
I
have
269 {appointments?} for each date. I'm not sure what the 269 is,
but I
was
expecting to see the number of appointments per date. It also does
not
appear to be giving a 7 day window. qCalendar is from 1/1/2007 to
7/1/2007
(this query will be used for other things). I was expecting the
query
we
are
working on to narrow that down to a 7 day window and count the
number
of
appointments per day. It seems like we are getting closer.

Thanks for your continued support.

:

Use a subquery.

For each date that you have an appointment, this query gives you
the
number
of appointments in the coming 7 days:

SELECT DateValue([tblCalendar].[StartDate]) AS TheDate,
(SELECT Count([AppointmentID]) AS TheCount
FROM tblCalendar AS Dupe
WHERE Dupe.StartDate >= DateValue([tblCalendar].[StartDate])
AND Dupe.StartDate < DateValue([tblCalendar].[StartDate]) + 8)
AS WeekAheadCount
FROM tblCalendar
GROUP BY DateValue([StartDate]);

If subqueries are new, see:
http://allenbrowne.com/subquery-01.html

I have tblCalendar (an export for Outlook). I am trying to find
the
number
of appointments within a 7 day window based on the start date of
the
appointment. I am converting the start date by Start Date:
CDate([StartDate]). The query is bring back the apporpriate
record
based
on
"Category" criteria and ">=Date() AND <=Date()+7", however, I
would
like
to
have "KT" be the number of appointments scheduled to take place
on
CDate([StartDate]). I tried several approaches but always get
strange
numbers such as 395 appointment on a given day when using: KTs:
DCount((FormatDateTime(CDate([StartDate]),0)),"qCalendar",FormatDateTime(CDate([StartDate]),0)>=Date())
in my query. (qCalendar filters out all non-biz related
appointments
from tblCalendar.)

 

[Allen Browne]

Forget the subquery, i.e. drop out that whole calculated field.

Instead just count the primary key field.

You want the count for the single date, not for the week ahead.

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

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

I made the changes. The query looks like:

SELECT CVDate([StartDate]) AS TheDate, (SELECT Count([Categories]) AS
TheCount FROM qCalendar AS Dupe WHERE Dupe.StartDate >=
CVDate([StartDate]) AND Dupe.StartDate < CVDate([StartDate]) + 8) AS
WeekAheadCount
FROM qCalendar
GROUP BY CVDate([StartDate])
HAVING (((CVDate([StartDate]))>=Date() And
(CVDate([StartDate]))<DateAdd("d",8,Date())));

The result looks like:

TheDate WeekAheadCount
4/9/2007 95
4/10/2007 95
4/12/2007 95
4/13/2007 95
4/14/2007 95

We will ned the current dat included in the window, but if I am reading
this
right, it is telling me I have 95 appointments on each day. Here is what
the
actual result should be for the next 7 days including today:

TheDate WeekAheadCount
4/8/2007 0
4/9/2007 2
4/10/2007 1
4/11/2007 0
4/12/2007 2
4/13/2007 0
4/14/2007 0

This will allow me to run a report that will result in something like:
... 7d KTs
... 5

Thanks for your support!

Re-reading this thread, I see you said:
I'm not sure what the 269 is, but I was expecting to see
the number of appointments per date.

The 269 is the total number of appointments in the 7 days ahead.
It seems you want a *daily* count for each of the 7 days, and not just a
total? If so, that will need a different approach.

If your goal is to list all the appointments for each of the next 7 days
in
a report, you don't want any grouping or counting at all. Just add
criteria
under your date field in query design limiting it to:

= Date() And < DateAdd("d", 8, Date())

The query give you each appointment in the week ahead. You can now create
the report grouping by the date field (each day) to show the
appointments.

Your assumption is correct. I do not need the time component. Let me
give
you a clearer picture. I am exporting my Outlook calendar to an Access
database. qCalendar gives me the appropriate information based on
Categories:

SELECT tblCalendar.StartDate, tblCalendar.StartTime,
tblCalendar.EndDate,
tblCalendar.EndTime, tblCalendar.Categories, tblCalendar.Subject,
tblCalendar.Description, tblCalendar.Location
FROM tblCalendar
WHERE (((tblCalendar.Categories)<>"Personal" And
(tblCalendar.Categories)<>"Holiday" And
(tblCalendar.Categories)<>"Birthday"
And (tblCalendar.Categories)<>"Phone Calls" And
(tblCalendar.Categories)<>"Business"));

qCalendar returns data that looks like:

StartDate Categories Subject StartTime EndDate EndTime Description
Location
1/3/2007 Recruiting Int: Marc Weinstien 1 ### ### #### 1:00:00
PM 1/3/2007 2:00:00 PM DF
1/3/2007 Recruiting Int: Greg Perc. 1 ### ### #### 2:30:00
PM 1/3/2007 3:30:00 PM "From Lake Villa
" DF
1/2/2007 Securities Mark J. 6:00:00 PM 1/2/2007 6:45:00 PM "Rollover
$114 from Hos.
$ 12K from bank
" Gur

{sorry for the long lines}

I am looking for qKT test to:
1) bring back a 7 day window of appointments for qCalendar
2) return a value KT (appointments) which are the total appointments
set
during the 7 day window.

You see, the idea is a report can eventually be printed that answers
the
question, "How many appointments do I have over the next 7 days?"

Right now qKT test looks like:

SELECT CVDate([StartDate]) AS TheDate, (SELECT Count([Categories]) AS
TheCount FROM qCalendar AS Dupe WHERE Dupe.StartDate >=
CVDate([StartDate]) AND Dupe.StartDate < CVDate([StartDate]) + 8) AS
WeekAheadCount
FROM qCalendar
GROUP BY CVDate([StartDate]);

and returns 70 lines that look like:

TheDate WeekAheadCount
1/2/2007 92
1/3/2007 92
1/4/2007 92
.
.
.
4/1/2007 92
4/3/2007 92
4/6/2007 92
4/7/2007 92
4/9/2007 92
4/10/2007 92
4/12/2007 92

So, it is not giving me the 7d window, and I have no idea what it means
by
WeekAheadCount being 92.

I know this is a lot of info, but now you have everything. Hopefully
the
problem was not simply insufficient data.

Thanks for helping!

:

Ah: I assumed you were trying to lose the time component.
Try CVDate() instead of DateValue, around *every* instance of
StartDate.

The fields all get to my database as text, thus the DateValue
function.
But
why the 269 value? If I could even get the number of appointment
per
day
for
every day returned by qCalendar I could get by just fine by changing
qCalendar to a 7 day window instead of a 6 month window. So, the
main
problem is counting the number of appointments per day.

Thoughts?

:

It should give you a 7-day window: greater than or equal to the
appointment
date, and less than the appointment date + 8 days -- that's a 7 day
period.

I guess something else must be going on, such as not narrowing to
the
desired person, or a confusion of data types (i.e. the fields are
not
real
date/time fields, or they are calculated fields Access is confused
about.)

The result of:
SELECT DateValue([StartDate]) AS TheDate, (SELECT
Count([Categories])
AS
TheCount FROM qCalendar AS Dupe WHERE Dupe.StartDate >=
DateValue([StartDate]) AND Dupe.StartDate <
DateValue([StartDate]) +
8)
AS
WeekAheadCount
FROM qCalendar
GROUP BY DateValue([StartDate]);

gives me two col. The first col, TheDate, gives me the dates
retrieved
via
qCalendar - works fine. The second col, WeekAheadCount, is
telling
me
I
have
269 {appointments?} for each date. I'm not sure what the 269 is,
but I
was
expecting to see the number of appointments per date. It also
does
not
appear to be giving a 7 day window. qCalendar is from 1/1/2007
to
7/1/2007
(this query will be used for other things). I was expecting the
query
we
are
working on to narrow that down to a 7 day window and count the
number
of
appointments per day. It seems like we are getting closer.

Thanks for your continued support.

:

Use a subquery.

For each date that you have an appointment, this query gives you
the
number
of appointments in the coming 7 days:

SELECT DateValue([tblCalendar].[StartDate]) AS TheDate,
(SELECT Count([AppointmentID]) AS TheCount
FROM tblCalendar AS Dupe
WHERE Dupe.StartDate >= DateValue([tblCalendar].[StartDate])
AND Dupe.StartDate < DateValue([tblCalendar].[StartDate]) +
8)
AS WeekAheadCount
FROM tblCalendar
GROUP BY DateValue([StartDate]);

If subqueries are new, see:
http://allenbrowne.com/subquery-01.html

I have tblCalendar (an export for Outlook). I am trying to
find
the
number
of appointments within a 7 day window based on the start date
of
the
appointment. I am converting the start date by Start Date:
CDate([StartDate]). The query is bring back the apporpriate
record
based
on
"Category" criteria and ">=Date() AND <=Date()+7", however, I
would
like
to
have "KT" be the number of appointments scheduled to take
place
on
CDate([StartDate]). I tried several approaches but always get
strange
numbers such as 395 appointment on a given day when using:
KTs:
DCount((FormatDateTime(CDate([StartDate]),0)),"qCalendar",FormatDateTime(CDate([StartDate]),0)>=Date())
in my query. (qCalendar filters out all non-biz related
appointments
from tblCalendar.)