Adding Field Using Multiple Criterion

[kkyork]

I have a union query to which I would like to add data from a related table. However, in order to pull the data in appropriately, I need to reference two fields in the underlying table to two fields in the query.

As a simple example, lets just say my query gives me teachers and classes by month. I'd like to add which class room the classes are held in. I have another table that shows the classrooms assigned to each teacher by month.

Query contains the following columns headings: TeacherID, Month, Class
Table contains the the following column headings: TeacherID, Month, Classroom



For lack of a better way of descibing what I'd like to do (and I'll tell you I am MUCH better in excel), I'd like to lookup the classroom assignments based on the TeacherID and the month and add it to my query.

Any ideas?

[June7]

You want output like:

TeacherID, Month, Class, Classroom

Why not a join of the query to the table, linking on both the TeacherID and Month fields?