cancel
Showing results for 
Search instead for 
Did you mean: 

Help with record selection/grouping

Former Member
0 Kudos

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

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

The easiest way would be to use an SQL Command that returns both the Task A and Task B data on one record. Something like (MS SQL):


select b.employee, b.taskid as task_b, b.task_status_b, b.marker as marker_b, b.date_modified as date_modified_b,
    a.taskid as task_a, a.task_status_a, a.marker as marker_a, a.date_modified as date_modified_a
from table a, table b
where a.employee = b.employee
and a.taskid = 'A'
and b.taskid = 'B'

HTH,

Carl

Answers (0)