Query Criteria

[Daryl2106]

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

[orange]

Please show the sql for your query.

[Daryl2106]

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;

[RayMilhon]

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

[orange]

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.

[Daryl2106]

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

[RayMilhon]

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.

[Daryl2106]

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

[RayMilhon]

You need to repost the query so we can see what changes you've made and how you made them.

[Daryl2106]

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

[RayMilhon]

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.

[Daryl2106]

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

[Daryl2106]

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