How to get differentiation of 2 records with the same RefNo? 1

[awinnn]

Hi,
I have RefNo and Amount.
This is sample of record,

1. A98512 1542.00
2. T54212 896.50
3. A98512 1250.00
4. C11523 672.00
5. C11523 985.00

I want to get differentiation of 2 records with the same RefNo.
Let say in the report, the record is grouped by RefNo. Is it possible to get differentiation of 2 records?
As for example,
RefNo TotAmount
A98512 292.00

TotAmount is equal to (1542.00-1250.00).

Any idea? Thanx in advance..

 

[ByteMyzer]

Set the Report's RecordSource to this statement:

SELECT RefNo, Max(Amount)-Min(Amount) AS TotAmount
FROM Table1
GROUP BY RefNo;


...of course, changing [Table1] to the actual name of the table.

This will work perfectly, on the premise that there are only two values for each RefNo in the table. If there are three or more instances of the same RefNo, this will only return the difference of the highest Amount and the lowest Amount.

 

[awinnn]

Hi..
thanx for reply..
it works because it only has either 1 or 2 values.

let say i have only 1 value, is it possible to return the value itself instead of return 0?

i've tested using IIF function..but it doesn't work..any idea?
anyway..thanx again..

 

[awinnn]

Hi,
i've solved the problem..
i've modified into this,

=IIf(Count([Amount])>1,Max([Amount])-Min([Amount]),[Amount])

it works..but i'm having to get the total on the report header..any idea?