composite key relationships

[Guest]

I have a table which lists information about student-rooms where the primary
key is a compostie key that lists the building, block, flat and RoomNo.

I have another table that lists the students that might stay in these rooms,
each students record also holds the data for the room they are staying in,
with the exact same setup as above. As rooms aren't tied to students they
have to be in separate tables & ive indexed the data in both tables so that a
combination of Building, block, flat and RoomNo cant be duplicated within
that table.

As these fields are the relational link between the 2 tables what i want is
for a combination of building, block, flat, RoomNo in the students table to
relate to a set in the students-rooms table.

This doenst seem to happen though as it will tell me if individual elements
doesn't have a corresponding value in the primary table but not if the
combination value has a match.

How can I do this?

With much begging and thanks

Amit

 

[John Vinson]

As these fields are the relational link between the 2 tables what i want is
for a combination of building, block, flat, RoomNo in the students table to
relate to a set in the students-rooms table.

This doenst seem to happen though as it will tell me if individual elements
doesn't have a corresponding value in the primary table but not if the
combination value has a match.

How can I do this?

You should be able to create a Query on the two tables, with four join
lines: building to building, block to block, flat to flat, room to
room. If you've set this relationship in the Relationships window
(again, with all four join lines) they should fill in automatically
when you create the query. If this isn't what you're seeing, or the
query isn't returning the expected results, please post the SQL view
of the query.

John W. Vinson[MVP]