Lookup query problem

[vsiat]

I have a Projects database with the following tables (among others):
* Project (ID, ProjectName, ...)
** ProjectAssignments (ProjectID, EmployeeID, ...)
** ProjectStatusReports (ProjectID, ReportDate, ReportedBy, Status, ...)

[ProjectAssignments] contains the employees a project is assigned to
[ProjectStatusReports] contains status reports for a specific project

My question is: how can I limit the contents of the [ReportedBy] lookup list
only to those employees assigned to the specific project?
A lookup query I created returns all the employess assigned to any project
in the database, not only the ones related to the current project and the
problem seems to be that I cannot reference somehow the current record in the
table [ProjectStatusReports]

 

[Ken Snell [MVP]]

You need to do this in a form, not in a table.

See http://www.mvps.org/access/forms/frm0028.htm