Query: Multiple Columns of Same Field with Different Criteria

[Legal_Eagle]

I would like to run a query that has multiple columns of the same field with different criteria.

My database has a table of employees with different titles (manager and associate). It has a many-to-many relationship to a table of projects using a junction table.

I'd like a report that lists each project in column 1, each manager assigned to such projects in column 2 and each associate assigned to such projects in column 3.

What concepts would I use to build the query that feeds into that report? I only know how to filter by one field, but column 2 and column 3 should display names filtered by their titles.

I'm a lawyer relearning Access after 15 years. This feels like such a basic question and I'm really frustrated trying to solve it. I really appreciate any input or tips you could give. Thanks!

[orange]

For clarity- 1 Mgr per project, 1 or Many Assoc per Project? Always good to show readers your Table structure.

Do you have a structure along these lines?

Project-->EmployeeRoleOnProject<--Employee


Project:
Proj1
Proj2

Employee:
Emp1
Emp2
Emp3

EmployeeRoleOnProject:
Proj1 Emp1 Mgr
Proj1 Emp2 Assoc
Proj2 Emp3 Assoc
Proj2 Emp2 Mgr

[Micron]

You may be over thinking the query part. Reports are better at grouping and sorting, plus they ignore query grouping anyway. Report grouping is usually done by having each group being one under the other, with the pertinent records (or sub groups) within that group. If you are not OK with that, then you may need a report with 1 or 2 subreports which contain the records side by side. If you mocked up something in Excel and copy/paste here it would create a table in your post. That might be useful in deciphering what you need. I agree with Orange that seeing your table relationships (or at least a pic of the tables in your query) might be helpful as well.

[Gicu]

You could create two separate queries (one listing the project ids and the managers, the other the project ids and the associates) and bring those in the third where you'd link on the project ids (probably using outer joins in case not all projects have both types).

Cheers,