Filtering Combo box items based on related table field- Please Help!

[morleyc]

Hi im stuck on filtering a combo box. I have users who can be assigned
a category. Jobs can be assigned a category and also have a list of
people working on that job (in the JobDetails table). I have the
following tables:

Categories Table:
CategoryID (PK)
Description

Users Table:
UserID (PK)
Username
CategoryID (FK on Categories.CategoryID)

Jobs Table:
JobID (PK)
CategoryID (FK on Categories.CategoryID)

JobDetails Table:
JobID (FK on Jobs.JobID)
UserID (FK on Users.UserID)

Then in the job details when listing users for a job (many users can
be for one job) i would like to only show the users which have the
same category as the jobs category. Is this possible? i tried the SQL
below for the lookup column field JobDetails.UserID but it doesnt
work:

SELECT Users.ID, Users.Username, Users.CategoryID
FROM Users, Jobs
WHERE (((Users.CategoryID)=[Jobs].[CategoryID]));

All the tables are linked with relationships but my SQL isnt so hot!
Any ideas as to how i would do this and get it working?

Even if it can be done, is this even recommended? I can see funny
conditions happening if the job details category changes or the users
category changes then even if they are existing in the job details
list they will not be shown? Even so, i would be interested in the
above to know how it is done (if possible).

Thanks in advance,

Chris