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

    Query to find dates that are out of sequence (Access 2002, SP3)

    I have attached a small sample database containing records with a school number, a student number, a sequence number, and a date. I would like to create a query that would help me locate records containing a date that is not in sequential order when sorted by student and within each student by their sequence number. Can this be done? The actual database where this needs to be done contains hundreds of thousands of records so visually looking at the records to find this information is not feasible.
    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: Query to find dates that are out of sequence (Access 2002, SP3)

    The following SQL for a query will do what you want:

    SELECT T1.Sch, T1.StuNum, T1.Seq, T2.Seq, T1.Effective, T2.Effective
    FROM tblEnrollment AS T1 INNER JOIN tblEnrollment AS T2 ON (T1.Effective > T2.Effective) AND (T1.Seq < T2.Seq) AND (T1.StuNum = T2.StuNum) AND (T1.Sch = T2.Sch);

    Because the query uses non-standard joins with > and < instead of =, it cannot be displayed in design view, only in SQL view and in datasheet view. If you would like to edit it in design view, temporarily change > and < to = and switch to design view. After editing, switch back to SQL view and restore the > and <.

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

    Re: Query to find dates that are out of sequence (Access 2002, SP3)

    I have an alternative solution that uses two queries in succession.

    The first uses prevmax: DMax("[Effective]","tblenrollment","([stuNum] ='" & [StuNum] & "') and ([seq] <=" & [seq] & ")")
    to find the the Prev Max date for each student up to the current seq number.

    Compare that to effective for each record in another field.

    The second query then select records where that comparison field is false.
    Regards
    John



  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query to find dates that are out of sequence (Access 2002, SP3)

    I never cease to be amazed at the answers you come up with Hans.

    I was waiting for an answer on this one as I was interested to see the approach! If it were in Excel a fucntion could be used to check the rows above for greater dates than the current row.

    I couldn't think of a way in Access to compare the records above with the current record!

    In contemplating this: Is it possible to use a record value in a different record to act as criteria in a query, other than the method you proved? (Just out of curiosity!!)
    Regards,
    Rudi

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

    Re: Query to find dates that are out of sequence (Access 2002, SP3)

    Rudi

    Have a look at this knowledge base article
    Regards
    John



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

    Re: Query to find dates that are out of sequence (Access 2002, SP3)

    John Hutchison has already posted a way to do that, using DMax. For other methods, see for example ACC2000: Referring to a Field in the Previous Record or Next Record.

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query to find dates that are out of sequence (Access 2002, SP3)

    <P ID="edit" class=small>(Edited by Rudi on 26-Oct-05 14:57. Sorry....I posted this to late....it is out of context now...based on the relpies already in! Thx John and Hans for the links!!!)</P>I guess John has answered my previous question!!! That DMax function is very interesting John...could you explain it step by step so i can grasp it! (I take that it is comparing the current record value with the previous record??)
    Regards,
    Rudi

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

    Re: Query to find dates that are out of sequence (Access 2002, SP3)

    The DMax function can be used in two ways:

    DMax("fieldname","tablename') will find the max value of the field in the table, looking across the whole table.

    Dmax("fieldname","tablename", Criteria) will find the max value of the field in the table for those records that match the criteria provided.

    So in this case, I sort by Student Number and Effective date.

    Then find the Dmax of EffectiveDate for those records with the same Student number as the current one, and a sequence number less than or equal to the current one. If they are in order the current effective date will equal the maximum.
    Regards
    John



  9. #9
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query to find dates that are out of sequence (Access 2002, SP3)

    Tx for the run through John. That helps!
    Cheers
    Regards,
    Rudi

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

    Re: Query to find dates that are out of sequence (Access 2002, SP3)

    Thanks so much for coding that made an impossible task so quick and easy. I tried to understand exactly what the coding is doing and how it is making the comparison but could not quite comprehend how it works. I could see that it is comparing each effective date to an alias copy of the table but other than that you lost me. Does the greater than and less than symbols make it look at the previous record when making the comparison?
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

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

    Re: Query to find dates that are out of sequence (Access 2002, SP3)

    The query uses two copies of the table, with aliases T1 and T2. They are joined the ordinary way on Sch and StuNum, that is the records are linked on student. The non-standard part of the join tells Access to look at records for which the Seq number in T1 is less than the Seq number in T2, while the Effective date in T1 is later than the Effective date in T2, i.e. the dates are out of sequence.

    This version will return any pair of out-of-sequence dates. If you only want to return two consecutive sequence numbers for which the dates are "wrong", you could use

    SELECT T1.Sch, T1.StuNum, T1.Seq, T2.Seq, T1.Effective, T2.Effective
    FROM tblEnrollment AS T1 INNER JOIN tblEnrollment AS T2 ON (T1.Effective > T2.Effective) AND (T1.Seq = T2.Seq - 1) AND (T1.StuNum = T2.StuNum) AND (T1.Sch = T2.Sch);

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

    Re: Query to find dates that are out of sequence (Access 2002, SP3)

    Thanks for taking the time to explain how the coding works. With a more complete understanding it will help me the next time I need to do this same kind of task.
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

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

    Re: Query to find dates that are out of sequence (Access 2002, SP3)

    Thanks John for sharing another way to accomplish this comparison. It is always good to know more than one solution to get the results you need.
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

Posting Permissions

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