Filter rows with field as parameter

[asu81]

Hello, I'm very new to Access and have a problem:

I have a table with fields as follows:
ProjectNo, EmployeeNo, [+many fields which display dates when the employee was finished with different tasks in said project]

Each project can have many employees, ie one row for each employee (but with the same projectNo)

I want to make a query that lists all the fields for projects that have a certain employee. I.e. I don't just want to list the rows that have the employeeNo in them - I want to list all project rows for projects which has a row with the certain EmployeeNo.

I figured one way was to
1) make a "temp" field in a query listing only the projects that include that EmployeeNo, and then
2) make a query on the original table that only lists the projects included in that "temp" field.

I've created the "temp" field with the following

SELECT tblProjects.ProjectNo AS temp
FROM tblProjects
WHERE tblProjects.EmployeeNo="xxxx";

but I don't know how to do the 2) part

Anyone has any advice? It's ok if you know an alternative simpler method

 

[boblarson]

If I understand correctly, I would have a form which you can select the employee from the combo box (and have the EmployeeNo as the bound column) and then use

Select * From YourTableNameHere
Where EmployeeNo = [Forms]![FormNameHere]![ComboBoxNameHere]

 

[asu81]

Thanks for your reply!

However, I believe your solution would only list the rows in which the EmployeeNo is equal to the value in the combo box, or am I mistaken?

In my table, there may be many rows with the same projectNo, but different EmployeeNo (many people in the same project).

I would like to enter an employeeNo, find out which projects that employee is working in, and then list ALL rows which correspond to these project numbers (ie not only the rows of that employee, but also the rows of other employees working in those projects).

 

[boblarson]

Two queries for that (or a subquery if you can go that route):

Select ProjectNo From TableNameHere Where EmployeeNo = [Forms]![FormNameHere]![ComboNameHere]

And then using that in another query

Select * From TableNameHere
Inner Join 1stQueryNameHere On TableNameHere.ProjectNo = 1stQueryNameHere.ProjectNo

 

[asu81]

That did the trick! thx