Linking 2 fields from different tables in MS access

Hi All,

I am creating a database at the moment and have all my tables and relationships created beautifully but my brain is grinding to a halt when trying to figure out one bit.

Now the database is for foreign students coming over and studying in the UK. And they are placed into a Care Home and Work, etc.

Now this database is coming from a previous Excel spreadsheet, where the Student Name and Care Home they are in is on every sheet.

Now I have created a Student table in access and a Care Home table as well as a number of others.

I am now created a form from another table called tbl_Payments, and I want to include the Student Name and Care Home field into this form.

However, I want these fields to be linked, so when they select a StudentName, it automatically picks up the care home they are in. I'm guessing I need to create a query for this but cant think of how to

Now teh Student Name field is already part of the payments tables fields, as well as the StudentID which is a foreign key, but the Care Home Name is not and needs to be brought in and linked with the Student Name, as the StudentID is just a number and wont mean much to the user.

Im sure this is very easy but I just can't see how I should be doing this.

Again, any help would be much appreciated.

Many Thanks

 

Thank you for the link, I had just forgot to set referential integrity between the relationships on the tables.

Many thanks for the response,