Max & Min within the same column

[Mary_S._(Guest)]

I have a dbf table with a column of integers - I would like to figure the difference between the largest and smallest number within that column. And to make it even more "simple" the first number in the column will always be the smallest, the last will be the largest. I tried the calc field __IIF(ID=1,integer, 0) the first record__ and__IIF(ID=19,integer,0) the last record____then a calc field substracting the 2 in a Summary band.____It got the last record right but the first yielded the false value - zero. When I moved the IIF statements to a record band then it recognized the ID numbers and brought back the correct integer but the substraction still didn^t use the first record (used zero instead). Bug or user error?? __I have R&R 8.1__Thanks for any assistance

[ColinDow]

Not sure how DBF works but I tend to use Max and Min functions for this sort of calculation Max(colname) Min(Colname). this means a change in data structure wont affect your report. I generally do this at the database level with SQL but Im pretty sure rr has this available from totals.

[Mary_S._(Guest)]

Thanks for replying and your suggestion.____I had also tried the MAX and MIN functions. These 2 functions in R&R require 2 expressions to compare. I tried as many variations I could come up with and either both came up with the same number for both functions or zero. My calcs were:__MAX(COLUMN,0)__MIN(COLUMN,MAX)__Both come up with the same number on record or group bands and zero in Summary.__

[ColinDow]

Sounds like you used the functions from the calc section (i.e. with the button that looks like a calculator!) This would evaluate on a line by line basis and be meaningless.____You need to use the totals section where you can create total expressions for a given group.____i.e Max of a field for all records and min of a field for all records (reset at grand total level).__Obviously the comparison of the two values is only valid when you get to the end of the group (u may be able to pre-process though)

[freitas]

[updated:LAST EDITED ON Aug-30-02 AT 08:55 AM (EST)]The feature MIN() and MAX() have two number that go with them. First is the field or calculated field that you want to use. The second is what you want the maximum or minimum to be.____Example: If you are doing a calculated field on some type of discount that you do not want to be greater then 50%, you would write min(discount,50) The opposite is true for MAX(). To prevent the discount from being less then 50%, you would write MAX(discount,50)______If you use the total selection you can pick and select how you want the MIN and MAX to work. You can select MIN MAX dates, number or whatever you want. This function is for footers or page totals. I use it for showing min and max job codes on page footers. If you want to see what I am talking about, view a pdf file I have at http://www.railsoft.com/pm.pdf and look at the bottom of the page for the minimum and maximum job codes.

[Mary_S._(Guest)]

Success! Thanks for leading me through to something new. I haven^t used - never needed to use - that function in totals and so stopped paying attention to it. That^ll show me! Thanks again.