Results 1 to 13 of 13
  1. #1
    Daryl2106 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2011
    Posts
    167

    Query Criteria

    Hi,

    I am working on a project for working with student data. I have a query that selects all students enrolled in all courses taught by a given instructor. It pulls records from a StudentEnrollment Table. For students who have withdrawn, the table includes a "Withdrawn" Field (Yes/No). I include the field in the querry with criteria set to "No". However, when I run the query I'm still getting the withdrawn students?

    I am new to access. Please help

    Take care,



    Daryl

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,847
    Please show the sql for your query.

  3. #3
    Daryl2106 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2011
    Posts
    167

    Sql

    Here it is:

    SELECT LU_Instructor.LastName, LU_Instructor.FirstName, CourseInventory.[Class Section], CourseInventory.[Course Title], StudentEnrollmentTable.StudentNumber, ContactInformation.FirstName, ContactInformation.LastName, StudentEnrollmentTable.Progress, StudentEnrollmentTable.GradeToDate, LU_Instructor.InstructorID
    FROM LU_Instructor INNER JOIN (ContactInformation INNER JOIN (CourseInventory INNER JOIN StudentEnrollmentTable ON CourseInventory.[Class Nbr] = StudentEnrollmentTable.ClassNumber) ON ContactInformation.StudentNumber = StudentEnrollmentTable.StudentNumber) ON LU_Instructor.InstructorID = CourseInventory.InstructorID
    WHERE (((LU_Instructor.InstructorID)=[Forms]![StudentEnrollmentByFacultyQF]![cboInstructorName]) AND ((StudentEnrollmentTable.Withdrawn)=No))
    ORDER BY CourseInventory.[Class Section], CourseInventory.[Course Title], ContactInformation.LastName;

  4. #4
    RayMilhon is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,085
    Try including the Withdrawn Field in your Select statment. See if any of the records returned have the withdrawn field set to yes. I'd almost be willing to bet they don't. If they don't post your DB here so we can look at it. If there's any sensitive Data remove it or replace it with dummy data

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,847
    I agree with Ray. I think you have to determine for certain exactly what value(s) are in the withdrawn field. I'm thinking -1 and 0, but let's see for sure.

  6. #6
    Daryl2106 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2011
    Posts
    167

    Copy of DataBase

    Hi,

    Sorry I couldn't reply sooner. I did try switching the criteria to False (-1) but it made no difference. I also tried adding the Withdrwn filed to the SELECT line but it didn't work either.

    Thanks for taking a look.

    Take care,

    Daryl

  7. #7
    RayMilhon is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,085
    First you have them reversed True = -1 False = 0. What do you mean it didn't work. the object was to put it into the select so you can see what is being returned to check your criteria. Ran the query and it worked fine for me. Although looking at your data structure the withdrawn field is totally unnecessary. You have a withdrawn date. If the withdrawn date is null withdrawn = false. If not null withdrawn = true.

  8. #8
    Daryl2106 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2011
    Posts
    167

    Still wondering

    Hi Ray,

    Thnaks for your reply. I only get to work on this project infrequently, so I apologize for the lateness of my response.

    When I run the query, I am still seeing those students with a discontinuation date.

    I am trying to get the query to return all "active" students, that is ones without a discontinuation date.

    ...and thanks for pointing out I don't need the withdrawn coulmn. I have a lot to learn.

    Thnaks and take care,

    Daryl

  9. #9
    RayMilhon is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,085
    You need to repost the query so we can see what changes you've made and how you made them.

  10. #10
    Daryl2106 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2011
    Posts
    167

    No Changes

    Hi,

    I really didn't make any changes. I deleted the "withdrawn" column and used the Withdrawn date column criteria set to Is NULL, but I am still getting withdrawn students in my query. I am using the form StudentEnrollmentbyFacultyQF to search student records and retrieve currents students. It seems like a simple problem I am overlooking.

    Take care,

    Daryl
    Attached Files Attached Files

  11. #11
    RayMilhon is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,085
    Well I don't know what to tell you. I took the original Db you provided changed the query to the withdrawn date is null and did not get any withdrawn students I can see.

  12. #12
    Daryl2106 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2011
    Posts
    167

    My Mistake

    Hi Ray,

    I looked more closely at several tables and it looks like I am having some problems with relationships (at least I think so) even though all my tables have referental intergrety. I need to do some trouble shooting myself. Thanks for all your help!

    Take care,

    Daryl

  13. #13
    Daryl2106 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2011
    Posts
    167

    Me Again

    Hi,

    Upon futher investigation, my data is correct. It seems to be a problem between the form and the query. The querry does run fine by itself but when I run it from the form it returns the withdrawn records as well.

    Any thoughts,

    Daryl

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 07-13-2011, 11:00 AM
  2. Query Criteria
    By BorisGomel in forum Queries
    Replies: 3
    Last Post: 06-08-2011, 08:35 AM
  3. Query Criteria
    By combine21 in forum Queries
    Replies: 12
    Last Post: 09-08-2010, 01:06 PM
  4. Query Criteria
    By jena in forum Access
    Replies: 1
    Last Post: 08-03-2008, 04:08 AM
  5. Query Criteria
    By jena in forum Queries
    Replies: 1
    Last Post: 04-29-2008, 11:00 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums