I'm just playing around in Access, as my database experience beyond simple flat files is fairly limited. I'm doing a relational database for a fictional company.
I currently have tblCases - containing all the 'jobs' or 'cases' the company is currently working on.
In this, I have two fields. tblCases.Assigned and tblCases.ProjectManager. The Assigned field has the value chosen (I'm operating this purely from Access at the moment - I'm more interested in the design than implementing it in a programming project) from a drop down menu, where the values are extracting from a lookup on another table (Just a simple "SELECT tblGroups.GroupID FROM tblGroups;" as the row source). So I might have the ability to choose group 1, 2 or 4, say.
For the ProjectManager field, I want this to give a drop down list where the values are all those employees (Stored in another table) who are in that group. I know I'm looking for something of form:
SELECT tblEmployees.Firstname, tblEmployees.Surname, tblEmployees.Department FROM tblEmployees WHERE (tblEmployees.Group=GROUP ASSIGNED FOR THIS ROW);
I'm just not certain how to get this to work... I tried
SELECT tblEmployees.Firstname, tblEmployees.Surname, tblEmployees.Department FROM tblEmployees WHERE (tblEmployees.Group=tblCases.Assigned);
But this just prompts me to enter the value for 'tblCases.Assigned' when I click the drop down arrow. Basically I'm wondering how I would construct a query, to generate the list of possible values for a field in tblCases, which would reference a value in another field on that same row?
P.S I tried the following
SELECT tblEmployees.Firstname, tblEmployees.Surname, tblEmployees.Department FROM tblEmployees WHERE (tblEmployees.Group=(SELECT tblCases.Assigned FROM tblCases);
Which works the way I want it to, but only if tblCases has one row - because it obviously returns all the rows for that query, rather than just the one corresponding to the row for which the query is being run. Which is logical!
Hope this is explained alright...