Replicating rows

[BB45]

CE 10 Ad Hoc
SQL 2000

I am trying to find a formula to help me with rows repeating the same data over and over. They are distinct because for every vehicle in that table I can have multiple drivers in our drivers table.

Currently I get:

VID Year Make Driver Title
1 2000 Nissan Fred Owner
1 2000 Nissan Wilma Spouse

I would like to see:

VID Year Make Owner Spouse
1 2000 Nissan Fred Wilma

I know I will end up with replication if I have drivers with the same title, but I can deal with that.

Thanks in advance for any help

BB45

 

[synapsevampire]

If there are only two per VID, then it's very simple.


Group by VID and in the group header use:

whileprintingrecords;
stringvar Owner:="";
stringvar Spouse:="";
stringvar Unknown:="";

In the details use the following formula:
whileprintingrecords;
stringvar Owner;
stringvar Spouse;
stringvar Unknown;
If {table.title} = "Owner" then
Owner:={table.Driver}
else
If {table.title} = "Spouse" then
Spouse:={table.Driver}
else
Unknown:= {table.Driver}


Then display all fields in the group footer, and the above by referencing the variables in 2 formulas:

whileprintingrecords;
stringvar Owner

whileprintingrecords;
stringvar Spouse

And if you want, you might toss in this one:
whileprintingrecords;
stringvar Unknown;

-k

 

[BB45]

Thank you very much I will give this a shot


BB45

 

[BB45]

I can make it work in Crystal 9 but using the Web Desktop in CE 10 does not allow me to drag fields into the footer. Is there a way to get other things in the footer using the web desktop?

Thanks
BB45