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 *
WHERE Class = ClassName
ORDER BY Date DESC;
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.
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.
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.
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:
SELECT TOP 1 a.Date, a.Class, a.Attendance
FROM ( SELECT TOP 30 ClassData.PK, ClassData.Date, ClassData.Class, ClassData.Attendance
ORDER BY ClassData.Date DESC ) AS a
ORDER BY a.PK;