multiple count of records within on recordset

[chad824]

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?

[MacDermott]

While you can use DCount() to get your counts without ever opening a
recordset, or open one recordset and apply various filters, you can also
simply use the Count() function in the group headers or footers of your
report, and avoid any worry about doing your counts separately.

"chad824" <c_a...@yahoo.com> wrote in message
news:1130529647....@o13g2000cwo.googlegroups.com...

[chad824]

I was using DCount before and it was slow, but when I used recordsets
it was still slow. So I figured it must be because I was opening one
at a time to get the six different counts.

I don't think I can group by date and use the footers to get a count
because I don't know if it is possible to group the dates by week of
month because it changes every month. If it is possible, I don't know
how to do it.

[James A. Fortune]

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

[chad824]

I'll try this out and let you know how it worked. It might be a couple
days, because I'm working on something else. No, thank you for posting
such an interesting solution.

[chad824]

James,

I hope you are still checking this thread because I wanted to thank you
for your solution. It works flawlessly and it is much quicker to group
the dates in the query than to use the recordset solution. I had to
modify a few thing to make it work with the report design, but you
certainly pointed me in the right direction.

Thanks again.

[jimfo...@compumarc.com]

I'm glad you got it working.

James A. Fortune

My Homepage (Merriam-Webster Word of the Day):
http://www.m-w.com/cgi-bin/mwwod.pl