asked on

SQL or Crystal repors to enable single row record

I will describe the issue as best I can!

I am not able to change the DB structure as it is a commercial DB.

Scenario: I have 2 tables, orders and consultants as follows

Orders
orderID
1001
1002
1003
etc

Consultants
person role
person1 sales
person2 Account manager
person3 sales
person4 Account manager
etc

These tables are linked, the issue is when running the query i get the following

orderID person
1001 person1
1001 person2
1002 person3
1002 person4

What I need to achieve is:

orderID Sales Account manager
1001 Person1 Person2
1002 person3 person4

Any pointers or help would be greatly appreciated

Many thanks in advance,

Keith.

Ido Millet

Insert, CrossTab.
Row: Order ID
Column: Role
Summarized Field: MAX of Person

kalees

ASKER

Hi Many thanks for your prompt reply.

While that goes some way to the perfect result when exported to csv it is not all in a single row so is not easy to work with as in reality I have more than just the orderID .

sorry for causing any confusion.

Do you have any further suggestion for addressing this?

Regards

Keith

Ido Millet

In that case, you probably need to create a formula for each possible column, group on Order ID, and arrange the formulas in the Group Footer. Suppress the detail.

ASKER CERTIFIED SOLUTION

Ido Millet

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial