I know this is most likely a very simple novice request, but I’ve banged my head on this for a few days and can’t figure it out: 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) which requires an input parameter from the user (type in the class name). I want to run a report in which I list all the classes with their most recent attendance, and I attached the query and the class name from my table. However, the query I have written still requires the input box parameter instead of producing a list of all classes and the most recent attendances:
Date Class Attendance
1/10/2014 Albertson 28
1/10/2014 Nomura 34
1/10/2014 Reynolds 33
1/10/2014 Maplebaum 25
The query I have that works is:
SELECT TOP 1 *
FROM ClassData
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.
An example of the table 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
I plan to use this report to produce charts, and to use this data for other calculations (% change in class size, for instance). As a secondary consideration (and significantly farther down the list), I'd like this report (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.
Date Class Attendance
1/10/2014 Albertson 28
1/10/2014 Nomura 34
1/10/2014 Reynolds 33
1/10/2014 Maplebaum 25
The query I have that works is:
SELECT TOP 1 *
FROM ClassData
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.
An example of the table 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
I plan to use this report to produce charts, and to use this data for other calculations (% change in class size, for instance). As a secondary consideration (and significantly farther down the list), I'd like this report (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.
