how to get the middle data of a set of data

[tandyaw]

hi all..

I have the following data in my database:
Factory1 Audit Date1
Factory1 Audit Date2
Factory1 Audit Date3

In my report, I need to display it like this:
Factory1 Audit Date1 Audit Date2 Audit Date3

I can get Audit Date1 and Audit Date3 using the minimum and maximum function and associate it with the factory name.

How can I get Audit Date2?

Thanks,
Lia

 

[sln007]

Do you have a 'fixed' number of "Audit Date" columns......
How many rows in your table? (thousands or Millions)

 

[tandyaw]

What do you mean with 'fixed' number?

There are currently only hundreds of rows, but it'll grow.

Thanks..

 

[drlex]

tandyaw,
I think sln007 is merely tying to find out more to determine the best way of organising your report.
If there was only ever going to be three audit dates for a factory, then the middle date is the one which is neither the minimum nor the maximum.
Since this is unlikely, the next question is "are you wanting to show all dates or just three".
If it's the former, then changing the report to a cross-tab ought to give you what you want.
If it's the latter, what is the selection criteria for the middle date?
lex

["] Veni, Vidi, Velcro. ["]

 

[sln007]

lex,
Right. If you know that there a fixed number of potential values, I've used a "running-count" within the first grouped column1....(that gives me a 1 -thru- X as a vaule)....then If 1 then new-field1 = value else "space/number", If 2 then new-field2 = value else "space/number", ....etc.
Then show footer for column1, (from properties, Layout tab)
ctrl-click & drag column1 value to footer,
insert "maximum(new-field2) for column1" into footer
insert "maximum(new-field2) for column2" into footer
insert "maximum(new-fieldX) for columnX" into footer
unselect "Details" box (so only the footers show)
.....and 'voila!!'.
I tested this process just now and it works. But again,
you have to have a fixed and known number of columns to work with.

 

[tandyaw]

Hi,

there was only every going to be three audit dates for a factory. And I think choosing one that is neither the minimum nor the maximum might work. Should've thought about that!
If not, then I'll try sln007's suggestion.
I just need to remember again for which report I asked this question for

Thanks guys!

Lia