Newbie Question: Want to populate fileds of one table based on one field in another.

[Tunesmithnw]

Hi all. Sure this has been asked before but I'm not even sure how to word it, much less search for it. Creating a photography tutorial db. So far have two tables: tblCategories that has CategoryID - Auto #/Primary Key and CategoryName - Text field/ListBox. I have already populated that table with the only 4 values (words) I want in it.
*
Have another table: tblSubject. SubjectID - auto #/primary key, SubjectName - text, and CategoryID - number. I do not have anything in this table yet.
*
All I want to do is create a query that will let me populate the tblSubject with the SubjectID, SubjectName, and select one of the 4 values from the tblCategories. I have tried everything I can think of but cannot get it to work. The list of values in the CategoryName feild is always blank.


Each Subject can have only one of the four Category values.
*
I'm doing it this way because I may need the tblCategory for other tables and queries that I intend to add to this db. Plus, now I'm determined to figure out how to do this.
Any suggestions would be very greatly appreciated.
Thanks so much.

[davegri]

tunesmith-davegri-v01.zip
How about this? Took about 10 minutes.
If you want it the other way around, with subject as the main form and category as the subform, the foreign key will have to be swapped to tblCategories, the relationships changed and the main/subform reconfigured.