compare two tables and display only non matching records from "school table" ??

[vivah]

SEE PICTURE




I have 3 tables (school, student and teacher) (teacher table may not be necessary here, i am just telling)


purpose of this to clean "school table' of those teachers who have no students with them, otherwise those teachers are prompted with school selections, instead it should straight goto their current school with students.




1. query should list teachers who have access to multiple schools by sch #.
2. query should list only teachers who dont have any students with them by comparing "school" and "student" table.


how is this possible? pls. help. Thanks

[ssanfu]

I would try an "unmatched query" using the "Find unmatched Query Wizard".

[Dal Jeanis]

First, there's a problem with your design. You have employee in the school table, and school in the employee table. That's a sign of redundant and erroneous data. You have both school and employee in the student table. Again, this is a sign that something is odd.

I also see that the user priveleges are in the SCHOOL table, rather than being associated with the employee. That is really odd.

Does the student really belong to the employee, or can a student have several employees associated with him? Is this one to many or many to many?

Does your employee go whatever school her student is at? Or does the student go to whatever school his employee is at? Or can they meet at a different school?

Usually, you would have an employee table, with the info for the employee (only), a school table, with the info for the school (only), a student table, with the info for the student (only), and then the tables that documented the relationships between them. User priveleges would usually be in a table of their own, associated with the employee, or would be on the employee record.

Head over to Access MVP Roger Carlson's site at http://rogersaccesslibrary.com, and read his quick tutorials on database design. Do the exercises on a couple of the sample databases, and then you'll know exactly what you have to do on your database.

I understand this isn't the question you asked, but it really is the advice that you were looking for. It will save you dozens of hours of banging your head against this odd design.

[vivah]

Thanks guys,
but I think i didn't explain here clearly, let me try again.

school table is the one i want to clean some records, for example : teacher '123456' has multiple school access that is '789, 790, 791 ' during login , but out of which only the ones with students in it for this teacher, must be taken or shown during login. nothing else. so the query should compare 'student table' and see is there any students for this teacher , if 'no' then display those teacher info along with school numbers from the 'school table'.

i did try unmatched query, but its coming blank, im making some errors, couldn't find.

[ssanfu]

Can you post a dB with the 3 tables (change/delete and sensitive info)? And save it in A2000 format?

[Dal Jeanis]

Vivah, we heard that explanation the first time. But you're missing the bigger picture. Your problem isn't what you think it is.

The reason you're having the problems using your database is that the design doesn't make any sense. There are fields replicated on the different tables that don't belong there.

What you call the "SCHOOL TABLE" is really your employee security permissions table. The School column should be deleted off that table, all duplicate records dropped, and the records would then be able to be unique. Thus, no problem.

The School column should also be deleted off either the student table or the teacher table. If a teacher always teaches at the same school, it should be on the teacher table. If not, then if a student always studies at the same school, then leave school on the student table, and the teacher will be considered to teach at whatever schools his students study at. If the same student may be taught by the same teacher at different schools, then you need to take "school" off both tables, and have a junction table that shows who, teaches whom, where.

In order to fix this, you - a person who understands your organization - need to review the entities that this database is representing, and make sure that each table properly represents ONE PARTICULAR ENTITY, and is properly labeled as to what that entity might be.

I'm willing to help you sort this out, and walk you through each step, but I'll only go through all that work if you're actually going to do the work to sort it out. Go to that link I showed you in post #3, read the tutorial, and you'll understand what I'm talking about.