Help with record selection/grouping

25 Views

I am trying to create a report as follows, but am running into some problems with my selection/grouping:

Data Structure (each line is a separate record; each employee has 2 records)

Employee TaskID TaskStatus Marker Date Modified

1 A CLOSED x

1 B OPEN N/A

2 A CLOSED y

2 B OPEN N/A

3 A CLOSED x

3 B CLOSED N/A

4 A CLOSED y

4 B CLOSED N/A

Report Requirements

I want a report that displays only employee number and the date that Task B was completed for all employees, and I want these records grouped based on Task B's Task Status (no problem. I did this).

However, I want to sub-group these Task B records (within Task Status) by the Marker field for Task A records! (I can't figure this out? If I only pull in Task B records, how can I compare what the associated employee has as a marker for their Task A record?)

Again, I only want to display data from the employee's Task B record, while subgrouping on a field value from the empoyee's Task A record. The report would be structured as follows:

Task B (OPEN), with Task A - Marker (x)

{Date Modified} Task B (OPEN), with Task A - Marker (y) {Date Modified}

Task B (CLOSED), with Task A - Marker (x)

{Date Modified} Task B (CLOSED), with Task A - Marker (y) {Date Modified}

Thanks.

Gary