Capture database row values in one group footer row

[thatgirlinMS]

I have a database that looks like below.
Client ID Eff Date Code Type Code
1 01/01/2010 I 193.3
1 01/02/2010 I 200.5
1 10/13/2009 III 120.8
2 01/01/2010 I 193.3
3 01/02/2010 I 200.5
3 10/13/2009 III 120.8

I am grouping by client ID (which is not the issue) but I need to fill in fields in the group footer with the database row values. How can I do that?

I need my data to show as

code 1 code 2 code 3 code 4 code 5 code 6

They all need to be seperate fields so they export correctly, meaning I can't accumulate the data and push it to a text box in a dlimited format.

Any help? Did I give you enough info?

I need to fill as many fields/report boxes as I can with database values - would some type of an array work? I know I can have UP TO 17 values, no more.

Thanks in advance!

 

[IanWaterman]

YOu can use a manual cross tab where you group data by client iD.

Then create 17 formula, repeat as below

@code1

If Codetype = I then code else 0

Suppress details

The place Maximum summaries of each of these formual in Group footer.

Ian

 

[thatgirlinMS]

Let me update you and tell you how I solved my problem. I might have gone around-the-world but it works and I'm stoked.

I created a dynamic array that adds only unique values. At the end I added a redim preserve and made the array as big as I needed it (17). I then used a join and delimited the values with an *

I then wrote 17 formulas for each column/data element I needed and within each I used split to get the wanted array element. The Diag1 formula would contain split(arrayformula,'*')[1] and would display thr first element.

I can post code if anyone is interested.

Thanks for the help!