chad824 said:
This is my current situation, which I use to produce a report that is
grouped by months and weeks of the month...(week 1, week 2, week
3....and so on) I can open a recordset that is based on a table and
query and count the number of records that have a date that fall within
that range.
Once I find the number of records in week 1, I close the recordset and
open another recordset to find the number of records within week 2 and
so on. Is this the most efficient way to do this? Can you open a
recordset and do all the record counting at one time before closing it
out?
Counting the records that fall within each month is not a problem, but
the records within each week is tricky because the way I have to do
this based on days that fall within the range from Sunday - Saturday
for the month. For example, for the purpose of this report, October
2005 has 6 weeks.
Week 1 is 10/1
Week 2 is 10/2 - 10/8
Week 3 is 10/9 - 10/15
Week 4 is 10/16 - 10/22
Week 5 is 10/23 - 10/29
Week 6 is 10/30 - 10/31
Would it be possible to count each of the groups of records from one
recordset?
Six weeks are the maximum possible.
Given:
tblInteger
ID Autonumber
I Integer
1 1
2 2
3 3
4 4
5 5
6 6
tblCurrentMonth
CurrentMonth Date
10/1/05
The following module functions are documented in:
http://groups.google.com/group/comp.databases.ms-access/msg/cec44318719fd06c?hl=en&
except for DaysInMonth which is a commonly used function in Access NG's.
'Begin Module Code-------
Public Function DaysInMonth(dtD As Date) As Integer
DaysInMonth = Day(DateSerial(Year(dtD), Month(dtD) + 1, 0))
End Function
Public Function NthXDay(N As Integer, d As Integer, dtD As Date) As Integer
NthXDay = (7 - WeekDay(DateSerial(Year(dtD), Month(dtD), 1)) + d) Mod 7
+ 1 + (N - 1) * 7
End Function
Public Function LastXDay(dtD As Date, DayConst As Integer) As Date
LastXDay = DateSerial(Year(dtD), Month(dtD) + 1,
(-WeekDay(DateSerial(Year(dtD), Month(dtD) + 1, 0)) + DayConst - 7) Mod 7)
End Function
'End Module Code-------
qryWeekNumberRanges:
SELECT I AS WeekNumber, (SELECT CurrentMonth FROM tblCurrentMonth) AS
dt, DateSerial(Year(dt), Month(dt), IIf(NthXDay(I - 1, 7, dt) <= 0, 1,
NthXDay(I - 1, 7, dt) + 1)) As WeekIStartDate, DateSerial(Year(dt),
Month(dt),IIf(NthXDay(I, 7, dt) > DaysInMonth(dt), DaysInMonth(dt),
NthXDay(I, 7, dt))) AS WeekIEndDate FROM tblInteger WHERE I <=
Day(LastXDay((SELECT CurrentMonth FROM tblCurrentMonth), 7)) \ 7 +
Abs(Day(LastXDay((SELECT CurrentMonth FROM tblCurrentMonth), 7)) Mod 7
<> 0) + Abs(DaysInMonth((SELECT CurrentMonth FROM tblCurrentMonth)) <>
Day(LastXDay((SELECT CurrentMonth FROM tblCurrentMonth), 7)));
!qryWeekNumberRanges:
WeekNumber dt WeekIStartDate WeekIEndDate
1 10/1/05 10/1/05 10/1/05
2 10/1/05 10/2/05 10/8/05
3 10/1/05 10/9/05 10/15/05
4 10/1/05 10/16/05 10/22/05
5 10/1/05 10/23/05 10/29/05
6 10/1/05 10/30/05 10/31/05
When CurrentMonth is changed to 9/1/05:
!qryWeekNumberRanges:
WeekNumber dt WeekIStartDate WeekIEndDate
1 9/1/05 9/1/05 9/3/05
2 9/1/05 9/4/05 9/10/05
3 9/1/05 9/11/05 9/17/05
4 9/1/05 9/18/05 9/24/05
5 9/1/05 9/25/05 9/30/05
I also tested it for 2/1/98:
WeekNumber dt WeekIStartDate WeekIEndDate
1 2/1/98 2/1/98 2/7/98
2 2/1/98 2/8/98 2/14/98
3 2/1/98 2/15/98 2/21/98
4 2/1/98 2/22/98 2/28/98
The query finds N, the number of weeks in the month, by using the date
of the last Saturday (day of month) to get the number of full weeks then
adding 1 for the initial week if it's not a multiple of 7, plus 1 more
for the final week if the last day of the month isn't the same as the
day of month of the last Saturday of the month. N is in the WHERE part
of the query and is used to limit the week numbers. I let the NthXDay
start from 0 instead of from 1 and use IIf to move the 0th Saturday
(always non-positive, plus one to get to a Sunday) up to the first day
of the month. I also move the final Saturday back to the date of the
end of the month when it goes past it. I only tested one other month
for current month. If I think of something simpler that doesn't add an
additional query I'll post it. I think the date ranges in this query
can be used as input/join for a Crosstab query or Totals query that can
display the counts you are looking for. Note that I use 7 instead of
vbSaturday in the NthXDay function since SQL doesn't know what
vbSaturday means. I didn't check to see if using something other than
vbSaturday would work when the week ends on other days. Thanks for
posting such an interesting problem.
James A. Fortune