 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 1 / February 2006Querying for No Records?
I'm building a database of students whose hearing will be tested. I had a main table set up of all of the student data and then a test table linked to the main table by a student ID. The way that the testers use the database is that while they are testing they only enter results for students who FAIL, thus creating records in the test table for each of those students. When they are finished they need to enter PASS for the students who passed the hearing test. There are thousands of students so they can't go through and enter thousands of passes. Is there a way to run a query to find students who don't have a record in the test table? Any help would be amazingly appreciated. Jake Your query is actually quite easy. Make a query, linking the ID Number from each table. Now double click on the link and a Join Properties window should open. Select the option that says "Include all records from MainTable and only those records from TestTable where the joined fields are equal." Make the ID field from the test table as one of your query fields along with the other fields your want from the main table. Put Is Null in the criteria for the Test Table ID. When you run the query, the results should give you all students that do not have any test results. Hope that helps! > I'm building a database of students whose hearing will be tested. I had > a main table set up of all of the student data and then a test table [quoted text clipped - 11 lines] > > Jake Perfect! This was exactly what I needed. Thank you so much. One more quick problem. I have created that query and it worked just like I needed it to. I now need to update all of those found students with a "P" for pass. When I run an update query on those students to replace the test field with a "P" it created the correct amount of records but instead of using the Student ID, it put a "0" in that field. Thoughts? You cannot do that with an update query because the student Id does not exist in the Test Table, remember? Use an append query instead. Append the StudentId from the MainTable and then the "P" for the grade. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.