Search code examples
databasems-accessreporting

How to create Report displaying unique records from multiple columns

I have a database with projects, team leads, and team members. I've set-up a query to produce a table like so:

|Project Name|Team lead|Team member|
____________________________________
|project A   |John     |Bob‎‎        |
|project A   |John     |Jane       |
|project B   |Bob      |John       |
|project B   |Bob      |Lisa       |
|project B   |Bob      |Mark       |
|project C   |Jane     |Bob        |
|project C   |Jane     |Harry      |

I'd like to create a Report that displays all the projects for a person, either as the team lead or a team member (and ideally identifies if they are the lead for the project). So it would look like something like:

 -John
   *Project A (lead)
   *Project B
 -Bob
   *Project A
   *Project B (lead)
   *Project C
 -Jane
   *Project A
   *Project C (lead)
 -etc.

Any idea of how to go about this? I'm thinking I probably need to create a different query to structure the data differently?

Solution

  • Build a UNION query. There is no query designer or wizard for UNION, must type into SQLView of query builder.

    UNION query rearranges data into normalized structure as should be in table.

    Using given query sample as source, something like:

    SELECT [Project Name], [Team lead] AS MemName, "LEAD" AS Role FROM queryname
UNION SELECT [Project Name], [Team member], Null FROM queryname;

    Use that query as source for a report with Sorting & Grouping.

    Might be able to use raw tables as source for UNION.