Report grouping based on same data in multiple fields

[aznvstr]

I have a table which I use to track tasks assigned to different members
of a group. Each group member can be an analyst or a reviewer. I am
trying to create one report which will show the group member's name at
the top and all tasks for which he is either the analyst or the
reviewer. The following is a simplifed example of my goal:

Table
Log # Task Analyst Reviewer
1 jump Bob Dave
2 sit Bob Bill
3 stand Bill Bob

Report
Bob
Log # Task Analyst Reviewer
1 jump Dave
2 sit Bill
3 stand Bill

Page break

Bill
Log # Task Analyst Reviewer
2 sit Bob
3 stand Bob

Page Break

Dave
Log # Task Analyst Reviewer
1 jump Bob

Any insight on this would be appreciated.

 

[Allen Browne]

The best solution would be to use a related table, with a field to indicate
the various types of tasks:
Log # foreign key to whatever job this is.
PersonID foreign key telling who this is.
RoleID "Analyst", "Reviewer", or whatever.

If you cannot build the table like that, you could fudge it with a UNION
query. Something like this:

SELECT [Log #], Task, Analyst AS Person, "Analyst" AS Role
FROM Table1
UNION ALL
SELECT [Log #], Task, Reviewer AS Person, "Reviewer" AS Role
FROM Table1;