How to convert transaction Rows into Columns 1

[dadazs]

Hello

I am working on transations table that logs staff appointments.
There can be three appointments (AP1, AP2, AP3) and termination.

My objective is to convert Staff Rows into columns e.g.

Staff Appointment1 Date1 Successful1
John AP1 12/01/2011 No
John AP2 12/02/2011 No
John AP3 15/02/2011 No
John Termination 16/02/2011 Yes

I need to convert to table like this

Staff Appointment1 Date1 Successful1 Appointment2 Date2 Successful2 Appointment3 Date3 Successful3 Termination

 

[Madawc]

I think you'll need to suppress detail lines and show the values in a group footer. Collect the data using formula fields, e.g.

if {appointment} = "AP1" then ToText({date}, "dd/MM/yyyy")
else " "

In the group footer, use a summary total to get the maximum for AP1_Date. And the same for the others.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[dadazs]

It worked well!
Please can you advise to transform tables physically?

 

[Madawc]

Crystal works with existing tables (datasets). If you're using it with SQL, you can have an SQL command that will re-arrange the data in new temporary tables, or even create new tables as part of the database.

The same is probably true with Oracle etc., but I only know SQL. And it should really be a new thread, since it is a new topic.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP