Sums (check example please hard to giev a title for this)

[Zeppi]

Hello there,

I have a report having 3 fields.

The titles are in the Page Header area and the data is in the Details area.

The report will result in a long list of figures.

Now one of these fields is a cumulative figure. Example below:

Field A - Field B - Field C
A 10 100
B 20 200
C 30 400

Now what i need is to creat a new field in the report that will calculate
the difference between the cumulative figures as shown below:

Field A - Field B - Field C - Field D
A 10 100 100 (1st figure has nothing to
delet from)
B 20 200 100 (200 - 100)
C 30 400 200 (400 - 200)

Hope this is clear.

It is very easy to do in Excel but cannot figure it out on Access.

 

[SnP]

Its best to create a query with all the calculations and then design the
report from the query.

 

[Golfinray]

Put a textbox on the form and right click it to get properties. Make the
control source:
=sum([field 1]-[field 2]-[field 3])

 

[Clif McIrvin]

Put a textbox on the form and right click it to get properties. Make
the
control source:
=sum([field 1]-[field 2]-[field 3])

Golfinray, I think Zeppi wanted to subtract the value from the previous
record (same column) in a report.

Do reports have a Current event like forms?

If this was a form, I'd suggest something like:
(I assumed Field C is a Double ... change the data type of LastD as
appropriate.)

Option Compare Database
Option Explicit

Dim LastD As Double ' Initializes to zero

Private Sub Form_Current()
Field_D = Field_C - LastD
LastD = Field_C
End Sub

but I don't have enough experience with reports yet to know if this can
be done. The Format event of the detail section, perhaps?

 

[John W. Vinson]

Hello there,

I have a report having 3 fields.

The titles are in the Page Header area and the data is in the Details area.

The report will result in a long list of figures.

Now one of these fields is a cumulative figure. Example below:

Field A - Field B - Field C
A 10 100
B 20 200
C 30 400

Now what i need is to creat a new field in the report that will calculate
the difference between the cumulative figures as shown below:

Field A - Field B - Field C - Field D
A 10 100 100 (1st figure has nothing to
delet from)
B 20 200 100 (200 - 100)
C 30 400 200 (400 - 200)

What's the data in the Table? It's not quite clear from your post what you're
working with!

What you may be able to do is have two Controls (reports don't *have* fields,
only tables do); the Control Source for one of them would be the table field
that you want to sum cumulatively, and you would set its Running Sum property
to "Over All" to get the running sum. The other would have a control source
using DLookUp or DSum to calculate the "previous" value. Since I don't really
see the table structure it's hard to be specific!

 

[Zeppi]

Will work on you tips and get back to you guys... Thanks all for the info.

As for the values they are simple numbers really which are entered directly
in the table as a cumulative increase.

I want to avoid having the user change them himself to the actual figures.

Will let you know and ask more if needed. Again thanks all.

 

[John W. Vinson]

As for the values they are simple numbers really which are entered directly
in the table as a cumulative increase.

I want to avoid having the user change them himself to the actual figures.

Just note that if the value *can* be calculated, then it *SHOULD* be
calculated and NOT stored.

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.