By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,491 Members | 1,868 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,491 IT Pros & Developers. It's quick & easy.

Querying for No Records?

P: n/a
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

Feb 21 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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!

Jake wrote:
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


Feb 21 '06 #2

P: n/a
Perfect! This was exactly what I needed. Thank you so much.

Feb 22 '06 #3

P: n/a
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?

Feb 22 '06 #4

P: n/a
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.

Feb 28 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.