Display records when two OTHER fields match

[pdowg881]

I currently have a query that uses a function to get the current users email address. This is matched with a "Teacher Email" address in a table of students to only show students which match the logged in teacher. I'd like to modify this so the teacher can see any student in their department. Is there a way to do this without creating a separate "Teachers" table that would contain a department and email?

[ItsMe]

Have you tried using the Query Designer to add the table of students to the query and join on the email address?

[cbende2]

I'm curious as to why wouldn't you want to create a separate teachers table?

[pdowg881]

Thanks. I just made the separate table and joined on department to do the job.

[pdowg881]

Whenever referencing this third field, I can't delete rows from my query or any form that use the query.

[ItsMe]

When you create a JOIN, you limit your options for updating fields in the query. You will not be able to delete a row. Also, I don't think you will be able to delete a row when a query uses a function in the criteria.

It kinda goes back to the question in post #3. Is your table structure normalized? Why would you attempt to delete a row that has the name of a student from one table and information from another table?