Results 1 to 10 of 10
  1. #1
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    Anaheim, California, USA
    Posts
    560
    Thanks
    17
    Thanked 2 Times in 2 Posts

    Not in Attendance

    I am using Access 2003 on Windows XP

    I have an attendance list, for our Sunday School students. Attendance is spotty, with not every student there every Sunday. I am able to create a report showing which students were there on a particular day. How do I create a report that shows which students were NOT there on a Sunday? Thx.

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    I presume you have a students table and a students attendance table. Make a query that shows no students attendances records for students, this is a type of query you can select, the query wizard will guide you through it.
    You will end up with something like:
    SELECT * FROM Students as s LEFT JOIN StudentsAttendances as sa ON s.StudentID = sa.StudentID WHERE sa.StudentID Is Null
    That is just aircode, see what you think

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    You may have to go with something like this if you are looking for a particular date:
    SELECT Students.StudentID, Students.NameofStudent
    FROM Students
    WHERE (((Students.StudentID) Not In (SELECT Students.StudentID FROM Students INNER JOIN StudentAttendances ON Students.StudentID = StudentAttendances.StudentID
    WHERE (((StudentAttendances.DateofAttend)=#9/8/2012#)))));

  4. #4
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    Anaheim, California, USA
    Posts
    560
    Thanks
    17
    Thanked 2 Times in 2 Posts
    Thanks Patt,

    I tried the second suggestion, in a new query, and I keep getting a syntax error. Here is what I used, with my actual field names, (yes, I know it isn't wise to have a space in a table or field name. I first created this about 10 years ago. One of these days I am going to re-create it ). I also changed the date to one that I had included.

    SELECT [Students.Student ID]
    FROM Students
    WHERE ((([Students.Student ID]) Not In (SELECT [Students.Student ID] FROM Students INNER JOIN tblAttendance ON [Students.Student ID] = [tblAttendance.Student ID]));
    WHERE (((tblAttendance.AttendanceDate)=#9/23/2012#))));

    It is telling me that the error is in this section,
    SELECT [Students.Student ID] FROM Students INNER JOIN tblAttendance ON [Students.Student ID] = [tblAttendance.Student ID])

    Any thoughts? Thank you.

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    You have:
    SELECT [Students.Student ID] FROM Students INNER JOIN tblAttendance ON [Students.Student ID] = [tblAttendance.Student ID])

    Try this:
    SELECT Students.[Student ID] FROM Students INNER JOIN tblAttendance ON Students.[Student ID] = tblAttendance.[Student ID])


    In you query:
    SELECT [Students.Student ID]
    FROM Students
    WHERE ((([Students.Student ID]) Not In (SELECT [Students.Student ID] FROM Students INNER JOIN tblAttendance ON [Students.Student ID] = [tblAttendance.Student ID]));
    WHERE (((tblAttendance.AttendanceDate)=#9/23/2012#))));

    Try this:
    SELECT Students.[Student ID]
    FROM Students
    WHERE (((Students.[Student ID]) Not In (SELECT Students.[Student ID] FROM Students INNER JOIN tblAttendance ON Students.[Student ID] = tblAttendance.[Student ID]));
    WHERE (((tblAttendance.AttendanceDate)=#9/23/2012#))));

  6. #6
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    Anaheim, California, USA
    Posts
    560
    Thanks
    17
    Thanked 2 Times in 2 Posts
    Hi Patt,

    Thank you so much for sticking with this. It still doesn't quite work. When I tried to run it, I got another syntax error and it pointed to the semi-colon after the first WHERE statement. I tried deleting it and changing it to a comma and still got a syntax error. I removed it and the WHERE and replaced them with AND, then it ran, however I didn't get any results and I know I have some students that were not there on that date. Any other suggestions? Thank you.

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    Sorry about that, delete that semicolon and see how you go.
    This is the select in my test database that works:
    SELECT Students.[Student ID], Students.NameofStudent
    FROM Students
    WHERE (((Students.[Student ID]) Not In (SELECT Students.[Student ID] FROM Students INNER JOIN StudentAttendances ON Students.[Student ID] = StudentAttendances.[Student ID]
    WHERE (((StudentAttendances.DateofAttend)=#9/8/2012#)))));
    Last edited by patt; 2012-09-27 at 18:21.

  8. #8
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Try this:

    Code:
    SELECT Students.[Student ID]
    FROM Students
    WHERE Students.[Student ID] Not In (SELECT Students.[Student ID] FROM Students INNER JOIN tblAttendance ON Students.[Student ID] = tblAttendance.[Student ID]
    WHERE tblAttendance.AttendanceDate=#9/23/2012#)
    Access will only run a single SQL statement, so using a semicolon in Access doesn't make much sense, IMO.

  9. #9
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    Anaheim, California, USA
    Posts
    560
    Thanks
    17
    Thanked 2 Times in 2 Posts
    Thank you Ruirib and Patt. It still took me a while to figure this out and get it to work. Using the SQL statement worked but I was getting hundreds of results, when I was only looking for 20 or so. I finally figured out what was going wrong, I was pulling from my Students table, which includes all students, past and present. What I did was, instead of using the Students table, I used qryActiveStudents and that solved it. I should have figured that out from the beginning but I still never would have gotten the structure right without your help. Thank you so much.

  10. #10
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    It was Patt's solution, actually, I just tweaked a small detail, but I am glad you got it running .

Posting Permissions

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