Add Num of Appointments for Week

[Guest]

I am having difficulty creating a query to att the number of appointment
scheduled for the coming week:

The window is a static Monday through Sunday.
The day the appointment was scheduled is stored in "Booked".
I need to add the number "Booked" in the range of Monday through Sunday,
i.e. today is 1/24/06. Books 1/23 - 1/29 are valid.
"Booked" is of type Date

 

[Vincent Johns]

Here's one way to do it...

Suppose your calendar contains the following dates. (Of course, you'd
want something more meaningful than the [Appointment_ID] field that I
show here, but this is just for illustration.)

[Appointment] Table Datasheet View:

Appointment_ID Booked
-------------- --------------
1734949431 Wed, 1/18/2006
1921820076 Thu, 1/19/2006
-1903057091 Sun, 1/22/2006
1243296698 Mon, 1/23/2006
1009154849 Wed, 1/25/2006
-547652704 Wed, 1/25/2006
144919246 Fri, 1/27/2006
1136735155 Sun, 1/29/2006
724101240 Mon, 1/30/2006

Then the following Query will count the dates according to the week in
which they appear.

[Q_NumAppts] SQL:
SELECT [Appointment]![Booked]
-((Weekday([Appointment]![Booked])+5) Mod 7) AS BegOfWeek,
Date()-((Weekday(Date())+5) Mod 7) AS ThisWeek,
Count(Appointment.Booked) AS NumberOfAppts
FROM Appointment
GROUP BY [Appointment]![Booked]
-((Weekday([Appointment]![Booked])+5) Mod 7),
Date()-((Weekday(Date())+5) Mod 7);

[Q_NumAppts] Query Datasheet View:

BegOfWeek ThisWeek NumberOfAppts
-------------- -------------- -------------
Mon, 1/16/2006 Mon, 1/23/2006 3
Mon, 1/23/2006 Mon, 1/23/2006 5
Mon, 1/30/2006 Mon, 1/23/2006 1

Since you want to see only the current week's dates, you can filter this
to return only the record in which the first 2 fields match.

[Q_This Week] SQL:

SELECT Q_NumAppts.ThisWeek, Q_NumAppts.NumberOfAppts
FROM Q_NumAppts
WHERE (((Q_NumAppts.ThisWeek)=[Q_NumAppts]![BegOfWeek]));

[Q_This Week] Query Datasheet View:

ThisWeek NumberOfAppts
-------------- -------------
Mon, 1/23/2006 5


-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.