Access Report

[subs]

paid amt transport amt Difference
22 23 -1
22 23
45 42 3
45 42
45 42
28 27 1
24 21 3
24 21

sum 6

i have 2 columns already in my access report - i.e paid amt and
transport amt. I want to add another column either in the query or
report called as difference and basically this column should calculate
the difference between paid amt and transport amt only for the first
set of nos. For example above example, 22 and 23 is present in two
rows. Difference should be calculated only in first row and not in
second row. Similarly when the set changes, i.e 45 and 42, difference
should be calculated only in the first row. This has to be done for
the entire set. Sum should be calculated at the end for the
difference column . How can i create this differenc colum in a report
as well as in the underlying query? Pls help thanks

 

[KARL DEWEY]

Do you have another field that defines the order? When you want to
calculated only in first row and not in second row you gotta have something
that distinguishes the two rows and their order.

 

[subs]

Do you have another field that defines the order?  When you want to
calculated only in first row and not in second row you gotta have something
that distinguishes the two rows and their order.

--
Build a little, test a little.







- Show quoted text -

hi

no i donot care about where the difference is posted in the difference
column as long as the difference is calculated in any one of the rows
and not the others. i donot want the duplicates. there is no order.-
thanks

 

[KARL DEWEY]

Add an autonumber field named Auto. Use these two queries --
subs_1 ---
SELECT SUBS.[paid amt], SUBS.[transport amt], Count(SUBS_1.[transport amt])
AS RankOrder
FROM SUBS LEFT JOIN SUBS AS SUBS_1 ON SUBS.[paid amt] = SUBS_1.[paid amt]
WHERE (((SUBS.Auto)>=[SUBS_1].[Auto]))
GROUP BY SUBS.[paid amt], SUBS.[transport amt], SUBS.Auto
ORDER BY SUBS.[paid amt], SUBS.[transport amt];

SELECT subs_1.[paid amt], subs_1.[transport amt], IIf([RankOrder]=1,[paid
amt]-[transport amt],Null) AS Difference
FROM subs_1;

 

[John W. Vinson]

there is no order.

Then you're out of luck. If there is no order then there is no "previous" or
"next" record and you can't distinguish them.