Request for one record only

[needmoremoney]

Hello all,

I'm pulling from two table. Table A have only one record per each employee which are things such as Id, name, and age. Table B holds multiple records for these employees such as books read, reports written and types.

When I pull records from Table B for my report, I get multiple records.

Example:

Id Name Age Books Reports Type
10 Jim 12 2 8 ter
10 Jim 12 3 3 fin
10 Jim 12 4 4 gil
11 Mona 13 1 3 ter
11 Mona 13 5 2
11 Mona 13 3 1 gil

What I need:

Id Name Age Books Reports Type1 Type2 Type3
10 Jim 12 9 15 ter fin gil
11 Mona 13 9 6 ter gil

I tried grouping and suppressing duplicate lines but it's not working for me. I might be doing it wrong though. I group by ID and then suppress duplicates. Any help thanks.

 

[synapsevampire]

Consider reading up relational database,

Gorup by the ID (use Insert Group).

Place all but the fields to be summarized in the group footer.

Place the fields to be summed in the details, right click each and select Insert->Summary->Sum for the group level.

Now suppress the details and the group header (right click each section and select Suppress).

-k

 

[lbass]

Follow SV's instructions, and then to get the type fields in the group footer, you could create conditional formulas like:

//{@ter}:
if {table.type} = "ter" then {table.type}

//{@fin}:
if {table.type} = "fin" then {table.type}

//{@gil}:
if {table.type} = "gil" then {table.type}

...and place them in the detail section and insert maximums on them at the group level.

-LB