Sum of multiple running totals/cummulative data

[DrDoIT]

Hello Forum,
Kindly assit me solve the following problem. i have read the posts on running totals but somehow i cant solve it right.

i have a table with health facilities (A,B,C,D) . each health facility has data from several months (Jan, Feb, Mar etc). the table has 2 fields (New Patients) and (Cumulative Patients) . Cumulative Patients is a total of New Patients for current month plus the total patients for the previous month.

In the Facility Footer of the report if i create text boxes with data =Sum([New Patients]) and =([Cumulative Patients]) works well.

However in the Report Footer =Sum([New Patients]) works but =Sum([Cumulative Patients]) totals everything. if i try =([Cumulative Patients]) it does not give an answer.

Kindly assist.

DrDoIT

 

[JHB]

However in the Report Footer =Sum([New Patients]) works but =Sum([Cumulative Patients]) totals everything. if i try =([Cumulative Patients]) it does not give an answer.

Kindly assist.

DrDoIT

What result do you want to get then, (show some data)?

 

[DrDoIT]

Hello Forum,the table format in the post below is totally messed up during posting.in summary the cumulative Patients at end of Feb is a sum of the new patients in February + patients end of Jan etcData is as below : Facility A : New Patients Jan (5) Feb (30). Cumulative Patients Jan (5), Feb (5+30 = 35)Facility B : New Patients Jan (20) Feb (25) Cumulative Patients Jan (20), Feb ( 20+25 =45)the program totals is a sum of Facility A and Facility Bthus for months Jan and Feb the report should read as follows :New Patients : Facility A 35, Facility B 4, Total New Patient 80Cumulative Patients : Facility A 35, Facility B 45 Total Cumulative Patients = 80However Sum([Cumulative Patients]) gives Facility A 40, Facility B 65 Total Cumulative Patients = 105.it is clearer in a table format, but i am not able to attach the table. i hope this explains it.Thanks.DrDoIT

 

[Mihail]

From your description I'm pretty sure that you have a wrong setup for your database.
Post the database with some relevant records.

 

[JHB]

....it is clearer in a table format, but i am not able to attach the table. i hope this explains it.Thanks.DrDoIT

Then post a printscreen from the table - then the explanation is really messed up.

 

[DrDoIT]

Hello Forum,

i have managed to attach the table.

The three health facilities have the following sub totals in the Facility Footer.

New York Hosp - 30
London Hosp - 40
Nairobi Hosp - 180

I would like the correct totals of 250 in the report footer as shown in the table but instead the formula =Sum([Cumulative Patients]) gives 533 .

I hope the explanation is now clear.

Thanks.

DrDoIT

 

[JHB]

But the sum of [Cumulative Patients] is exactly 533.
10+15+30+8+20+40+100+130+180=533.
If you want it to give 250 then use Sum([New Patients]) for the field.

 

[DrDoIT]

Thanks JHB, Mihail.

as Mihail noted earlier - it's most likely the issue of the design. i will redefine the fields.

DrDoIT

 

[DrDoIT]

But the sum of [Cumulative Patients] is exactly 533.
10+15+30+8+20+40+100+130+180=533.
If you want it to give 250 then use Sum([New Patients]) for the field.

is there a way to make it just sum the 30+40+180 and ignore the rest of the figures?

this is because the Cumulative Patients 30 already contains the New Patients 10 and 15; cumulative 40 already contains the New Patients 8 and 20 etc