Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

multiple count of records within on recordset

Discussion in 'Information Technology' started by chad824, Oct 29, 2005.

  1. chad824

    chad824
    Expand Collapse
    Guest

    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?
     
  2. Loading...


  3. MacDermott

    MacDermott
    Expand Collapse
    Guest

    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_all_1@yahoo.com> wrote in message
    news:1130529647.757056.81370@o13g2000cwo.googlegroups.com...
    > 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?
    >
     
  4. chad824

    chad824
    Expand Collapse
    Guest

    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.
     
  5. James A. Fortune

    James A. Fortune
    Expand Collapse
    Guest

    chad824 wrote:
    > 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
     
  6. chad824

    chad824
    Expand Collapse
    Guest

    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.
     
  7. chad824

    chad824
    Expand Collapse
    Guest

    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.
     
  8. jimfortune@compumarc.com

    jimfortune@compumarc.com
    Expand Collapse
    Guest

    chad824 wrote:
    > 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.


    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
     

Share This Page