Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2014
    Posts
    20

    Unanswered: Select the nth most recent record

    I have an Access 2007 database table which lists the attendance for each class and on each date for a school. I have a working query to select the 1st most recent record by date (i.e. most recent class attendance). However, I want to be able to select the nth day’s most recent record for a specific class (i.e. the class attendance 30 days ago, or 365 days ago) and I just can't figure it out. The new query would be for a specific number of days in the past (i.e. the query would be specific for 30 days, and not changeable). I need these queries to run statistics on changes between two standard dates (one year, one semester, one month, one week, etc.).

    The query I have that works is:

    SELECT TOP 1 *
    FROM ClassData
    WHERE Class = ClassName
    ORDER BY Date DESC;

    I tried to adapt this by increasing “TOP 1*” to “TOP 30 *” and then trying to reorder this group in reverse to take the TOP 1 * of this group (the 30th record) ala Selecting Records Based on Row Number in SQL: ASP Alliance
    , but I was not able to make it work.

    The database is schooldata.mdb
    The table is ClassData
    The Class is in ClassData.Class
    The Date is in ClassData.Date
    The Attendance is in ClassData.Attendance
    (Each Class in ClassData has many other columns beyond Attendance and Date such as ClassData.Absences.)
    For any date and class there will be only one attendance number.

    An example is below:

    Date Class Attendance
    1/7/2014 Albertson 22
    1/7/2014 Nomura 26
    1/7/2014 Reynolds 25
    1/7/2014 Maplebaum 32
    1/8/2014 Albertson 25
    1/8/2014 Nomura 22
    1/8/2014 Reynolds 33
    1/8/2014 Maplebaum 23
    1/9/2014 Albertson 30
    1/9/2014 Nomura 23
    1/9/2014 Reynolds 27
    1/9/2014 Maplebaum 28
    1/10/2014 Albertson 28
    1/10/2014 Nomura 34
    1/10/2014 Reynolds 33
    1/10/2014 Maplebaum 25

    As a secondary consideration (and significantly farther down the list), I'd like this user defined function (and whatever code -- VBA or other) to be fast/efficient because eventually this table will have tens of thousands of entries as I expand to additional years and schools.

    Thank you for your help on this.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    The TOP predicate is used to select the n first rows. If you want to select n days (considering that there can be more than one row for the same day), you can use a criteria such as:
    Code:
    WHERE [Date] >= [some date];
    OR
    Code:
    WHERE [Date] Between [first date] And [last date];
    Note: Date is a reserved word and should not be used to name a column.
    Have a nice day!

  3. #3
    Join Date
    Jan 2014
    Posts
    20

    Automate query

    Thank you for your response.

    If I understand correctly you are suggesting that we simply run a query between two fixed dates? If so, that would be fine except for a piece of information which I must have failed to provide: the data dates will always be marching forward (i.e. "today" is a different date every day) and sometimes the dates are irregular (occasionally there is a holiday or snow day which would have been a normal school day but we skip for the purposes of this list). Also, I would like to remove as much operator error as we can. So we are trying to find a more-automated way of simply picking the nth-from-the-top record as sorted by date.

    I tried a few ways but was unsuccessful (all which make sense to me but I must be screwing up the code).

    1) Query the records for the top 30 school days, and then reorder the dates as ascending instead of descending.

    2) Count 30 record-dates back and select that record.

    I should point out that I am a novice in Access (as if this wasn't immediately apparent to all the readers of this forum); I am probably just making some basic mistakes.

    Thank you for your help.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    If the table has a primary key (it should, always) you can do it in 2 steps:
    1. Select the 30 top records ordered by date DESC.
    2. select the top 1 record from 1 ordered by primary key (PK) ASC.
    You can do it in a single query, like this:
    Code:
    SELECT TOP 1 a.Date, a.Class, a.Attendance 
    FROM ( SELECT TOP 30 ClassData.PK, ClassData.Date, ClassData.Class, ClassData.Attendance
           FROM ClassData
           ORDER BY ClassData.Date DESC ) AS a
    ORDER BY a.PK;
    Have a nice day!

  5. #5
    Join Date
    Jan 2014
    Posts
    20

    Great, thank you

    Thank you for this. I was able to modify this code to get it working on my database. I apologize for my delay in thanking you.

  6. #6
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    No problem, you're welcome!
    Have a nice day!

Posting Permissions

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