Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Finding Records With Sequential Dates (Access 2002, SP3)

    I have been assigned the task to find any students with 15 consecutive absences. I have attached a sample database where I can look at the qryAbsOver15ByDayCnt and manually look to see if any students fall into this category by counting absences only if they are on sequential dates and restarting the count for each student if the dates are not sequential. Does anyone know if there is a way to automatically pull a list that would provide me with this information.
    Attached Files Attached Files
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Finding Records With Sequential Dates (Access

    What exactly do you mean by 15 consecutive days - 15 consecutive calendar days, e.g. December 1 through December 15, or do you want to exclude weekends and holidays?

  3. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding Records With Sequential Dates (Access

    The 15 sequential days are actual school days as found in tblCalendar. That table excludes weekends, holidays, and vacation days.
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Finding Records With Sequential Dates (Access

    I have created two new queries in the attached database.
    qryAbsences merely joins the two tables.
    qry15days returns the student number and the dates from which there are sequences of at least 15 consecutive dates from tblCalendar. There may be more than one record per student if the sequences are very long.
    Attached Files Attached Files

  5. #5
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding Records With Sequential Dates (Access

    I have been studying the database you attached to try and modify the calculation in qry15days so that the max number over 15 consecutive days would appear so that each student would appear only once with the maximum number of consecutive absences. I have been unsuccessful. Can this be done?
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Finding Records With Sequential Dates (Access

    The query qryMaxRun in the attached database will do that, but it is EXTREMELY slow, even with a limited number of records. When the number of records increases, the slowness will increase dramatically.
    Attached Files Attached Files

  7. #7
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Finding Records With Sequential Dates (Access

    Here is another solution.

    This opens a DA O recordset based on qryAbsences processes them in order. Any absences of more than 15 days are written to another table.
    Attached Files Attached Files
    Regards
    John



  8. #8
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding Records With Sequential Dates (Access

    Thank you both for all your help. I used John's method because of the speed. I was able to come up with the student list who had 15 or more sequential absences from a list of 173,195 records in just seconds.
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Finding Records With Sequential Dates (Access

    This is a situation in which VBA is much better than using a query.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •