I am using 2007.  I am an intermediate user.  I have a database with a project information table.  In this table there is a field for Leader.  I also have tables connected to this table by project name.  One table is for Team and one is for Consultant.  Each of these tables can have multiple entries for each project.  I want to develop a query that will tell me which projects I am involved with and in what capacity ie Leader, Team or Consultant.  So far, every query I have tried will return these however it will return one line for each record showing my name and I just want the project name and the capacity that I am involved in.  Here is an example of what I don't want.  


Project     Leader     Team       Consultant

A            Me        Harris       Scott
A            Me        Harris       James
A            Me        White        Scott
A            Me        White        James
B            White      Me          James
B            White      Me          Scott
C            James      Scott        Me
C            James      White        Me

I want to just return

Project     Leader     Team       Consultant

A            Me        

B                       Me

C                                  Me


Hope this makes sense and thanks for your help.
 

---

Answer from Richard Rost:

Create a query and set up a criteria that only returns records in which you are the leader, team, or consultant. That's the easy part.

Now create new calculated fields using the IIF function that only show "Me" or a an empty string if it's not "Me."

=IIF(Leader="Me","Me","")

Now, take those results and create a group-by query (aggregate) so you only see one record per project.

Here are some tutorials that might help you with these concepts:

http://599cd.com/tips/access/calculated-query-fields?key=BlFAlEx

http://599cd.com/tips/access/iif-function?key=BlFAlEx

http://599cd.com/tips/access/aggregate-query?key=BlFAlEx